If you’re not intending on keeping the stored procedure, I’d use an anonymous PLSQL block: BEGIN –Bye Sequences! FOR i IN (SELECT us.sequence_name FROM USER_SEQUENCES us) LOOP EXECUTE IMMEDIATE ‘drop sequence ‘|| i.sequence_name ||”; END LOOP; –Bye Tables! FOR i IN (SELECT ut.table_name FROM USER_TABLES ut) LOOP EXECUTE IMMEDIATE ‘drop table ‘|| i.table_name ||’ CASCADE … Read more
SELECT CASE WHEN age <= 10 THEN ‘1-10′ WHEN age <= 20 THEN ’11-20′ ELSE ’21+’ END AS age, COUNT(*) AS n FROM age GROUP BY CASE WHEN age <= 10 THEN ‘1-10′ WHEN age <= 20 THEN ’11-20′ ELSE ’21+’ END
You probably want DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function SQL> ed Wrote file afiedt.buf 1 create or replace procedure p1 2 is 3 begin 4 raise_application_error( -20001, ‘Error 1’, true ); 5* end; SQL> / Procedure created. SQL> create or replace procedure p2 2 as 3 begin 4 null; 5 p1; 6 end; 7 / Procedure created. SQL> begin … Read more
It could be correct, but that would depend on how much data you have. Typically I would create an index for the columns I was using in a GROUP BY, but in your case the optimizer may have decided that after using the field_two index that there wouldn’t be enough data returned to justify using … Read more
Note: This code is untested Define a record for your refCursor return type, call it rec. For example: TYPE MyRec IS RECORD (col1 VARCHAR2(10), col2 VARCHAR2(20), …); –define the record rec MyRec; — instantiate the record Once you have the refcursor returned from your procedure, you can add the following code where your comments are … Read more
In PLSQL block, columns of select statements must be assigned to variables, which is not the case in SQL statements. The second BEGIN’s SQL statement doesn’t have INTO clause and that caused the error. DECLARE PROD_ROW_ID VARCHAR (10) := NULL; VIS_ROW_ID NUMBER; DSC VARCHAR (512); BEGIN SELECT ROW_ID INTO VIS_ROW_ID FROM SIEBEL.S_PROD_INT WHERE PART_NUM = … Read more
Oracle (and PostgreSQL) have: %TYPE %ROWTYPE %TYPE %TYPE is used to declare variables with relation to the data type of a column in an existing table: DECLARE v_id ORDERS.ORDER_ID%TYPE The benefit here is that if the data type changes, the variable data type stays in sync. Reference: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/fundamentals.htm#i6080 %ROWTYPE This is used in cursors to … Read more
This is a simple way to do it without any triggers or sequences: insert into WORKQUEUE (ID, facilitycode, workaction, description) values ((select max(ID)+1 from WORKQUEUE), ‘J’, ‘II’, ‘TESTVALUES’) It worked for me but would not work with an empty table, I guess.
You could do this in PL/SQL: declare l_nullable user_tab_columns.nullable%type; begin select nullable into l_nullable from user_tab_columns where table_name=”MYTABLE” and column_name=”MYCOLUMN”; if l_nullable=”N” then execute immediate ‘alter table mytable modify (mycolumn null)’; end if; end;
You cannot directly use a SQL statement in a PL/SQL expression: SQL> begin 2 if (select count(*) from dual) >= 1 then 3 null; 4 end if; 5 end; 6 / if (select count(*) from dual) >= 1 then * ERROR at line 2: ORA-06550: line 2, column 6: PLS-00103: Encountered the symbol “SELECT” when … Read more