Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2007
    Posts
    64

    Thumbs down Unanswered: Passing a Dynamic variable in OPEN CURSOR statement

    Hi Friends ,

    I have a procedure as below...
    create procedure proc1
    (
    rct1 in out SYS_REFCURSOR
    )
    as
    var nvarchar2(500);
    begin
    var :='select i from tab1';
    open rct1 for var;
    end;

    When i try to compile this ,it is throwing error

    PLS-00382: expression is of wrong type
    I came to know that only SELECT statements should be passed to OPEN CURSOR --clause. In the above procedure the variable var is also a SELECT statement ...please suggest me how to handle this .

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool nvarchar2?

    What about this nvarchar2 thing?
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by sushma007
    I came to know that only SELECT statements should be passed to OPEN CURSOR --clause. In the above procedure the variable var is also a SELECT statement ...please suggest me how to handle this .
    Just a small demonstration:
    Code:
    SQL> create procedure proc1
      2  (
      3  rct1 in out SYS_REFCURSOR
      4  )
      5  as
      6  var nvarchar2(500);
      7  begin
      8  var :='select i from tab1';
      9  open rct1 for var;
     10  end;
     11  /
    
    Warning: Procedure created with compilation errors.
    
    SQL> sho err
    Errors for PROCEDURE PROC1:
    
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    9/1      PL/SQL: Statement ignored
    9/15     PLS-00382: expression is of wrong type
    Code:
    SQL> create procedure proc1
      2  (
      3  rct1 in out SYS_REFCURSOR
      4  )
      5  as
      6  var varchar2(500);
      7  begin
      8  var :='select i from tab1';
      9  open rct1 for var;
     10  end;
     11  /
    
    Procedure created.
    
    SQL> sho err
    No errors.
    Conclusion: do not use NVARCHAR2 (or any unicode type) for storing the SELECT statement. It is meaningless for statement identifiers anyway. If you are using unicode parameter (which is not the case you posted), bind it.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by LKBrwn_DBA
    What about this nvarchar2 thing?
    What's the matter with that? It's a regular Oracle datatype.

    http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements001.htm#i45685

  5. #5
    Join Date
    Feb 2009
    Posts
    62
    DATE and LONG are standard Oracle datatypes too, but you wouldn't expect to be able to use them in this case.

    To be fair, you have to do a bit of digging to find that it's only Varchar2 and char that it accepts

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •