OracleInfo

From Kb

Contact Article Author | Blog of Article Author | FirstPartners.net Home | LinkedIn profile of Author

Contents 1 See Also 2 Oracle Reference Guides 3 Oracle System Tables 4 Debugging from Oracle 5 Calling Oracle Functions 5.1 Executing Stored Procedure from SQL Navigator 5.2 Returning Resultset from Oracle Function 5.3 Limiting the number of rows returned from Oracle SQL Query 6 Oracle Cursors 6.1 Dynamic Oracle SQL in PL-SQL Function

See Also

Useful Scripts for SQLPlus | TOAD for Oracle

Oracle Reference Guides

• Oracle Functions

Oracle System Tables

-- data dictionary is a reference guide to all the Oracle system tables
SELECT * FROM dictionary

SELECT * FROM all_objects
SELECT * FROM all_tables
SELECT * FROM user_synonyms

Get all the constraints currently applied to a table

SELECT 'TABLE '||substr(c.table_name,1,35)|| '  '||
       a.column_name || '  ' ||  c.constraint_name 
FROM user_constraints c, user_tables u,  user_tab_columns a 
WHERE c.table_name = u.table_name 
AND a.table_name = c.table_name 
AND c.table_name = 'TABLE_NAME';

Debugging from Oracle

// USE custom info
sidebug(1,'operation',operation_i);
SELECT * FROM SI_ERRORS;
// OR USE standard DBMS Output
DBMS_OUTPUT.PUT_LINE('Some Text :' || some_cursor.VALUE );

Calling Oracle Functions

Executing Stored Procedure from SQL Navigator

EXEC PACKAGE.prodecure_name(params)

DECLARE 
  RetVal types.ref_cursor;
  PARAM_1 VARCHAR2(200);
 
BEGIN 
  PARAM_1 := 'somevalue';
 
  RetVal := DB_NAME.FUNCTION_NAME ( PARAM_1 );
 
  :rc0_RetVal := RetVal;
 
  COMMIT; 
END;

Returning Resultset from Oracle Function

Note: can't return resultset from a Stored Procedure

CREATE OR REPLACE FUNCTION SOME_FUNCTION(var1 NUMBER, var2 VARCHAR2)
RETURN types.ref_cursor AS result types.ref_cursor;
 
CURSOR some_curs IS SELECT * FROM SOME_TABLE;
 
BEGIN
 OPEN types.ref_cursor  SELECT * FROM SOME_TABLE;
 RETURN types.ref_cursor
END SOME_FUNCTION;
/

or , to return a generated resultset

OPEN some_curs FOR SELECT 1 AS COL1, 1 AS COL2 FROM DUAL;
  RETURN some_curs ;

Limiting the number of rows returned from Oracle SQL Query

SELECT * FROM TABLE WHERE rownum<100;

Oracle Cursors

Open Cursor using variable - from http://download.oracle.com/docs/cd/B14117_01/appdev.101/b10807/13_elems034.htm

DECLARE
   TYPE EmpCurTyp IS REF CURSOR;  -- define weak REF CURSOR type
   emp_cv   EmpCurTyp;  -- declare cursor variable
   my_ename VARCHAR2(15);
   my_sal   NUMBER := 1000;
BEGIN
   OPEN emp_cv FOR  -- open cursor variable
      'SELECT ename, sal FROM emp WHERE sal > :s' USING my_sal;
   ...
END;

Dynamic Oracle SQL in PL-SQL Function

This approach allows you to build up your SQL as a String. Look at using Oracle Cursors with variable (previous section) first , as it gives better performance. Use this approach if you don't know the table (for example)

CREATE OR REPLACE FUNCTION someFunction(table_name IN VARCHAR)
    RETURN TYPES.REF_CURSOR
AS
 
 sql_cursor       TYPES.REF_CURSOR;
 dyn_sql          VARCHAR2(2000);
 
BEGIN
 
 dyn_sql := 'SELECT * from ' ||
      ' table_name ' 
 
    OPEN sql_cursor FOR dyn_sql ;
    RETURN sql_cursor;
END;