IF EXISTS() is semantically incorrect. EXISTS condition can be used only inside a SQL statement. So you might rewrite your pl/sql block as follows: declare l_exst number(1); begin select case when exists(select ce.s_regno from courseoffering co join co_enrolment ce on ce.co_id = co.co_id where ce.s_regno=403 and ce.coe_completionstatus=”C” and ce.c_id = 803 and rownum = 1 … Read more
EXCEPTION WHEN DUP_VAL_ON_INDEX THEN UPDATE
Short of granting access to DBMS_LOCK.sleep, this will work but it’s a horrible hack: IN_TIME INT; –num seconds v_now DATE; — 1) Get the date & time SELECT SYSDATE INTO v_now FROM DUAL; — 2) Loop until the original timestamp plus the amount of seconds <= current date LOOP EXIT WHEN v_now + (IN_TIME * … Read more
This is one way to do it: SQL> set serveroutput on SQL> CREATE OR REPLACE TYPE MyType AS VARRAY(200) OF VARCHAR2(50); 2 / Type created SQL> CREATE OR REPLACE PROCEDURE testing (t_in MyType) IS 2 BEGIN 3 FOR i IN 1..t_in.count LOOP 4 dbms_output.put_line(t_in(i)); 5 END LOOP; 6 END; 7 / Procedure created SQL> DECLARE … Read more
In my opinion, a dynamic PL/SQL block is somewhat obscure. While is very flexible, is also hard to tune, hard to debug and hard to figure out what’s up. My vote goes to your first option, EXECUTE IMMEDIATE v_query_str INTO v_num_of_employees USING p_job; Both uses bind variables, but first, for me, is more redeable and … Read more
This can be done with a single statement and some XML magic: select table_name, to_number(extractvalue(xmltype(dbms_xmlgen.getxml(‘select count(*) c from ‘||owner||’.’||table_name)),’/ROWSET/ROW/C’)) as count from all_tables where owner=”FOOBAR”
Here is how to build a function that returns a result set that can be queried as if it were a table: SQL> create type emp_obj is object (empno number, ename varchar2(10)); 2 / Type created. SQL> create type emp_tab is table of emp_obj; 2 / Type created. SQL> create or replace function all_emps return … Read more
In SQL Plus: SQL> create procedure myproc (prc out sys_refcursor) 2 is 3 begin 4 open prc for select * from emp; 5 end; 6 / Procedure created. SQL> var rc refcursor SQL> execute myproc(:rc) PL/SQL procedure successfully completed. SQL> print rc EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ———- ———- ——— ———- ———– … Read more
DBMS_OUTPUT is not the best tool to debug, since most environments don’t use it natively. If you want to capture the output of DBMS_OUTPUT however, you would simply use the DBMS_OUTPUT.get_line procedure. Here is a small example: SQL> create directory tmp as ‘/tmp/’; Directory created SQL> CREATE OR REPLACE PROCEDURE write_log AS 2 l_line VARCHAR2(255); … Read more
PL/SQL: numeric or value error: character string buffer too small is due to the fact that you declare a string to be of a fixed length (say 20), and at some point in your code you assign it a value whose length exceeds what you declared. for example: myString VARCHAR2(20); myString :=’abcdefghijklmnopqrstuvwxyz’; –length 26 will … Read more