Home » SQL & PL/SQL » Client Tools » error when passing values to the query (oracle 19.7, Redhat Linux)
error when passing values to the query [message #685789] Thu, 24 March 2022 12:55 Go to next message
shrinika
Messages: 302
Registered: April 2008
Senior Member
Hello, I want to run the below query and i am passing the value for variable :b1, :b2, & :b3.

For :b3, i am passing oracle table type. somehow it is not working. can you please help me how to pass the
value for :b3?

SQL> var B3 varchar2(20);
SQL> var B2 varchar2(30);
SQL> var B1 varchar2(30);
SQL> exec :B3:='ERETRY'

PL/SQL procedure successfully completed.

SQL> exec :B2:='999999999999999999999999'

PL/SQL procedure successfully completed.

SQL> exec :B1 := REPORT_GEN.T_VARCHAR2_TAB('TEST')
BEGIN :B1 := REPORT_GEN.T_VARCHAR2_TAB('TEST'); END;

             *
ERROR at line 1:
ORA-06550: line 1, column 14:
PLS-00382: expression is of wrong type
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


SQL>

SQL> desc REPORT_GEN.T_VARCHAR2_TAB
 REPORT_GEN.T_VARCHAR2_TAB TABLE OF VARCHAR2(32767 CHAR)

SQL>
i want to run the below query.

SELECT *
FROM PRDTABLE PRD, 
TABLE (:B1 ) PRVD_NAME_ARR 
WHERE PRD.REQUEST_ID = :B2 
AND NOT EXISTS (SELECT 1 FROM REQ WHERE REQUEST_ID = PRD.REQUEST_ID AND ICS_RFLAG = :B3 ) 
AND PRD.PROVIDER = PRVD_NAME_ARR.COLUMN_VALUE;
Re: error when passing values to the query [message #685790 is a reply to message #685789] Thu, 24 March 2022 13:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68203
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> help var

 VARIABLE
 --------

 Declares a bind variable that can be referenced in PL/SQL, or
 lists the current display characteristics for a single variable
 or all variables.

VAR[IABLE] [variable [type]]

 where type represents one of the following:

     NUMBER         CHAR          CHAR (n [CHAR|BYTE])
     NCHAR          NCHAR (n)     VARCHAR2 (n [CHAR|BYTE])
     NVARCHAR2 (n)  CLOB          NCLOB
     REFCURSOR      BINARY_FLOAT  BINARY_DOUBLE
SQL*Plus does not know table variables so you can't do it with SQL*Plus.

Why don't you do it in one shot, without :B1:
SELECT *
FROM PRDTABLE PRD, 
TABLE (REPORT_GEN.T_VARCHAR2_TAB('TEST')) PRVD_NAME_ARR 
WHERE PRD.REQUEST_ID = :B2 
AND NOT EXISTS (SELECT 1 FROM REQ WHERE REQUEST_ID = PRD.REQUEST_ID AND ICS_RFLAG = :B3 ) 
AND PRD.PROVIDER = PRVD_NAME_ARR.COLUMN_VALUE;

[Updated on: Thu, 24 March 2022 13:49]

Report message to a moderator

Re: error when passing values to the query [message #685791 is a reply to message #685790] Thu, 24 March 2022 14:47 Go to previous messageGo to next message
shrinika
Messages: 302
Registered: April 2008
Senior Member
Thank you Michel for the response!

Why don't you do it in one shot, without :B1:
################################################
The sql is using full table scan in my database and locked index plan(using SPM).
But still it is not picking up the plan which i locked in SPM. Then i opened SR with
oracle. Oracle engineer wanted me to run the sql as it is called from application and
ask me to generate some trace.

Hence i am trying to the run the sql same way it is called in the application.
Re: error when passing values to the query [message #685792 is a reply to message #685791] Thu, 24 March 2022 14:52 Go to previous messageGo to next message
shrinika
Messages: 302
Registered: April 2008
Senior Member
i am coming back to my original problem...

I still need help on declaring this variable.

SQL> desc REPORT_GEN.T_VARCHAR2_TAB
 REPORT_GEN.T_VARCHAR2_TAB TABLE OF VARCHAR2(32767 CHAR)

SQL> var B1=TABLE
SP2-0552: Bind variable "b1" not declared.
SQL>  var B1=REPORT_GEN.T_VARCHAR2_TAB
SP2-0552: Bind variable "b1" not declared.
SQL> var B1=T_VARCHAR2_TAB
SP2-0552: Bind variable "b1" not declared.
SQL>
Re: error when passing values to the query [message #685793 is a reply to message #685792] Thu, 24 March 2022 16:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68203
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I still need help on declaring this variable.
As I said, it is NOT possible, so this is a dead end with SQL*Plus.

Re: error when passing values to the query [message #685794 is a reply to message #685793] Thu, 24 March 2022 17:20 Go to previous messageGo to next message
shrinika
Messages: 302
Registered: April 2008
Senior Member
got it. thanks!
Re: error when passing values to the query [message #685805 is a reply to message #685794] Mon, 28 March 2022 09:25 Go to previous message
Frank
Messages: 7892
Registered: March 2000
Senior Member
What you are trying to do in sqlplus can easily be done in a few lines in a PL/SQL block, using PL/SQL variables where you have your binds.
Previous Topic: Results_Set output through the spool
Next Topic: PL/SQL Developer settings FAQ
Goto Forum:
  


Current Time: Tue Jun 28 09:50:12 CDT 2022