New answers tagged

0

You don't need loops, but one CASE per searched value: select ... from ( select ... case when 'Bach' IN (col1,col2,col3,col4,col5) then 1 else 0 end + case when 'Joan' IN (col1,col2,col3,col4,col5) then 1 else 0 end + case when 'Mike' IN (col1,col2,col3,col4,col5) then 1 else 0 end as matches from tab ) dt where matches > 0 ...


4

NUM is not a reserved word. The best way is not to use reserved words. If you insist on using them, you can put them between quotation marks. This does not work: SET SERVEROUTPUT ON declare begin number; begin begin := 10; DBMS_OUTPUT.put_line('The value of begin ' || begin); end; / This does: SET SERVEROUTPUT ON declare "begin" number; ...


0

Closest thing that I know is to overload a function... create or replace package test_pkg as function variableParams ( a in varchar2, z in integer, y in integer ) return integer; function variableParams ( a in varchar2, z in integer, y in integer, x in integer ) return integer; end; / create or replace package body test_pkg as function ...


1

DECODE is an SQL function, not PL/SQL. You can not use it in plain PL/SQL, only as part of SQL statements. There is no nice and easy way for this. There is the method with collections, for example: create or replace type t_numbers as table of number; / create or replace function sum_plsql (p_numbers t_numbers) return number as rv number := 0; begin ...


0

Is the DBMS Output disabled by the DBA? Try running this in SQL*Plus SET SERVEROUTPUT ON SIZE 30000; EXEC PROCEDURE_1('SYN_NAME'); --replace with name of synonym you want to find You can also add DBMS_OUTPUT.ENABLE (buffer_size => NULL); to your procedure like this CREATE PROCEDURE PROCEDURE_1 AS strName VARCHAR2(50); CURSOR C_NAME IS SELECT ...


0

No grant needed. You do not see any results, because output is disabled by default. Enable output before running your code: set serveroutput on


1

You shouldn't need any special privilege to view the ALL_* views. If the procedure is owned by and is running as USER1 then it could be that USER1 has not been granted access to any synonyms. If the procedure is owned by a user other than USER1 it could be that that user hasn't been granted access to any synonyms. It all comes down to who's credentials ...


0

Just join by i and j, and multiply val. If the samples are incomplete in either matrix, you may want to grab the union of both matrices and left join. with A AS (...), B as (...), C AS (select i, j from A union i, j from B) select C.i, C.j, COALESCE(A.val * B.val, 0) AS val from C left join A on (A.i = C.i AND A.j = C.j) left join B on (B.i = C.i AND ...


1

Input (A, then B): set pages 12 with A as ( select ceil(rownum/3) as i, mod(rownum -1 , 3) + 1 as j, rownum as val from dual connect by level <= 9 ), B as ( select ceil(rownum/3) as i, mod(rownum -1 , 3) + 1 as j, 12 - rownum as val from dual connect by level <= 9 ) select A.i, A.j, A.val from A union all select B.i, B.j, B.val from B ; ...


0

connect system / [PASSWORD] as sysdba; grant create session to [USER]; grant execute on DBMS_OUTPUT to [USER] with grant option; I can solved with it.



Top 50 recent answers are included