Home » SQL & PL/SQL » SQL & PL/SQL » error in package
error in package [message #249493] Wed, 04 July 2007 12:10 Go to next message
ddkdhar
Messages: 68
Registered: February 2007
Member

when i execute below package it gives like error


the package given below


CREATE OR REPLACE PROCEDURE test_fetch_ref_cur (p_query VARCHAR2 :=
NULL) IS
   l_statement VARCHAR2 (32000);

   FUNCTION process_def RETURN VARCHAR2 IS

      l_process_def VARCHAR2 (32000);
   BEGIN
      l_process_def := 'DBMS_OUTPUT.PUT_LINE (';

      FOR i IN 1 .. dyn_fetch.g_count LOOP
         l_process_def := l_process_def || ' l_record.col_' || i || ' ||
''>>'' || ';
      END LOOP;

      l_process_def := RTRIM (l_process_def, ' || ''>>'' || ') || ');';
      RETURN l_process_def;
   END;
BEGIN
   test.set_query (p_query);
   dyn_fetch.describe_columns;

   l_statement :=
   '  DECLARE'                              ||
   '     TYPE record_t IS RECORD ('         ||
            dyn_fetch.record_def || ');'    ||
   '     l_record  record_t;'               ||

   '     l_ref_cur dyn_fetch.ref_cur_t;'    ||
   '  BEGIN'                                ||
   '     l_ref_cur := test.ref_cur;'        ||
   '     LOOP'                              ||
   '        FETCH l_ref_cur INTO l_record;' ||
   '        EXIT WHEN l_ref_cur%NOTFOUND;'  ||
            process_def                     ||
   '     END LOOP;'                         ||
   '     CLOSE l_ref_cur;'                  ||
   '  END;';

   EXECUTE IMMEDIATE l_statement;
END;
/



----------------------------------------------------------


CREATE OR REPLACE PACKAGE dyn_fetch IS
   TYPE ref_cur_t IS REF CURSOR;


   g_query    VARCHAR2 (32000);
   g_count    NUMBER;
   g_desc_tab DBMS_SQL.DESC_TAB;

   varchar2_type CONSTANT PLS_INTEGER := 1;
   number_type   CONSTANT PLS_INTEGER := 2;
   date_type     CONSTANT PLS_INTEGER := 12;
   rowid_type    CONSTANT PLS_INTEGER := 11;
   char_type     CONSTANT PLS_INTEGER := 96;

   long_type     CONSTANT PLS_INTEGER := 8;
   raw_type      CONSTANT PLS_INTEGER := 23;
   mlslabel_type CONSTANT PLS_INTEGER := 106;
   clob_type     CONSTANT PLS_INTEGER := 112;
   blob_type     CONSTANT PLS_INTEGER := 113;
   bfile_type    CONSTANT PLS_INTEGER := 114;

   PROCEDURE describe_columns;
   FUNCTION record_def RETURN VARCHAR2;
END;

/

CREATE OR REPLACE PACKAGE BODY dyn_fetch IS
   PROCEDURE describe_columns IS
      l_cur INTEGER;
   BEGIN
      l_cur := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE            (l_cur, g_query, DBMS_SQL.NATIVE);
      DBMS_SQL.DESCRIBE_COLUMNS (l_cur, g_count, g_desc_tab);
      DBMS_SQL.CLOSE_CURSOR     (l_cur);
   EXCEPTION

      WHEN OTHERS THEN
         IF DBMS_SQL.IS_OPEN (l_cur) THEN
            DBMS_SQL.CLOSE_CURSOR (l_cur);
         END IF;
         RAISE;
   END;

   FUNCTION record_def RETURN VARCHAR2 IS
      l_record_def    VARCHAR2 (32000);
      l_type          VARCHAR2 (100);
      l_col_type      PLS_INTEGER;
      l_col_max_len   PLS_INTEGER;

      l_col_precision PLS_INTEGER;
      l_col_scale     PLS_INTEGER;
   BEGIN
      FOR i IN 1..g_count LOOP
         l_col_type      := g_desc_tab(i).col_type;
         l_col_max_len   := g_desc_tab(i).col_max_len;
         l_col_precision := g_desc_tab(i).col_precision;
         l_col_scale     := g_desc_tab(i).col_scale;

         IF    l_col_type = varchar2_type THEN
            l_type := 'VARCHAR2(' || l_col_max_len || ')';
         ELSIF l_col_type = number_type THEN
            l_type := 'NUMBER(' || l_col_precision || ',' || l_col_scale

|| ')';
         ELSIF l_col_type = date_type THEN
            l_type := 'DATE';
         ELSIF l_col_type = rowid_type THEN
            l_type := 'ROWID';
         ELSIF l_col_type = char_type THEN
            l_type := 'CHAR(' || l_col_max_len || ')';
      -- ELSIF  l_col_type = ...
            -- long_type, raw_type ...
         END IF;

         l_record_def := l_record_def || ' col_' || i || ' ' || l_type
|| ',';
      END LOOP;


      l_record_def := RTRIM (l_record_def, ',');
      RETURN l_record_def;
   END;
END;
/
----------------------------------------------------------


CREATE OR REPLACE PACKAGE test IS

   PROCEDURE set_query (p_query VARCHAR2 := NULL);
   FUNCTION ref_cur RETURN dyn_fetch.ref_cur_t;
END;
/

CREATE OR REPLACE PACKAGE BODY test IS
   PROCEDURE set_query (p_query VARCHAR2 := NULL) IS
      l_query VARCHAR2 (32000) :=
      '  SELECT e.empno, e.ename,'   ||
      '         e.deptno, d.dname'   ||
      '    FROM emp  e,'             ||
      '         dept d'              ||
      '   WHERE e.deptno = d.deptno';
   BEGIN
      IF p_query IS NULL THEN
         dyn_fetch.g_query := l_query;
      ELSE

         dyn_fetch.g_query := p_query;
      END IF;
   END;

   FUNCTION ref_cur RETURN dyn_fetch.ref_cur_t IS
      l_ref_cur dyn_fetch.ref_cur_t;
   BEGIN
      OPEN l_ref_cur FOR dyn_fetch.g_query;
      RETURN l_ref_cur;
   END;
END;
/




Re: error in package [message #249496 is a reply to message #249493] Wed, 04 July 2007 12:19 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>when i execute below package it gives like error
Error? What error?
I don't see any error; therefore no problem exists.

P.S.
If/when the error message include a LINE number, you'll need to post the code which include line numbers!
Re: error in package [message #414768 is a reply to message #249496] Thu, 23 July 2009 07:40 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Don't report messages to the moderators do get quicker answers, that's not what the "Report Message" function is for.

Answer the question anacedent asked instead.

[Updated on: Thu, 23 July 2009 07:41]

Report message to a moderator

Re: error in package [message #414770 is a reply to message #249493] Thu, 23 July 2009 07:43 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
@ThomasG

Was it really the OP who reported this?
cause if you look it's over 2 years old.
Re: error in package [message #414774 is a reply to message #414770] Thu, 23 July 2009 07:46 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Oooops. Didn't catch that.

The report was an about 2-page unformatted mess that basically also started along the lines of "when i execute below package it gives like error......", so I didn't double-check.

Sorry.
Previous Topic: Mutating Error In Trigger
Next Topic: ORA-00907: missing right parenthesis
Goto Forum:
  


Current Time: Fri Dec 09 19:22:10 CST 2016

Total time taken to generate the page: 0.11166 seconds