Re: DBMS_SQL and select count(*) into :x - error ORA-01006

From: Roy Varghese <rvarghese_at_ibm.net>
Date: 1997/11/30
Message-ID: <3481fd8e.0@news1.ibm.net>#1/1

 Hi
I think I have figured out problem in your script. However I have not tried running it. I have marked the portion in the script below.

Osipov N.F. wrote in message ...
>Hi!
>
>I ask you to help, if there will be a time.
>
>In the documentation:
>Oracle7 Server Application Developer's Guide, chapter 10 Using Dinamic SQL
>I did not manage to find the answer for the decision of a task.
>
>It is necessary to create a procedure for definition of quantity of records
>in different tables.
>In these tables is identical column - date _ rec (date)
>
>With run of a procedure recno_tab there is a error
>ORA-01006 Bind variable does not exist
>
>Prompt, please, in what here error?
>
>create or replace procedure recno_tab
>( tname in varchar2, date_a in date, n_row out number) as
> i number;
> x number;
> n number;
> cmd varchar2 (200);
>begin
> i: = dbms_sql.open_cursor;

---

> cmd: ='select count (*) into :x from '||table_name||
> ' where date_rec='||date_a||";

---

Error seems to be in this statement. When date_a is concatenated to date_rec it is converted to string automatically by Oracle. So the final value of 'cmd' would be :
'select count(*) into :x from YOUR_TABLE where date_rec=28-DEC-97'
Note that the quotes are missing from the date value.
Now Oracle is interpreting 28 as a variable which needs to be binded, hence the error !
Soln:
You should write:
> cmd: ='select count (*) into :x from '||table_name||
> ' where date_rec=''' ||date_a|| ' '' ';

Hope that solves problem

> dbms_sql.parse (i, cmd, dbms_sql.v7);
> dbms_sql.bind_variable (i, ':x', n);
> x: = dbms _ s(i))
> dbms_sql.close_cursor(i))
> n_row: = n;
>end;
>
>Very much I ask to answer on e-mail.
>
>Yours faithfully, Nikolai Osipov
>Yamburg Tyumen Russia
>nfo_at_yamb.ygaz.tyumen.su
>
>
>
>
>
>
>
>
Received on Sun Nov 30 1997 - 00:00:00 CST