Home » SQL & PL/SQL » SQL & PL/SQL » count in dynamic SQL (merged 2)
count in dynamic SQL (merged 2) [message #306489] Fri, 14 March 2008 08:46 Go to next message
aditiC
Messages: 38
Registered: February 2006
Location: INDIA
Member
Hi All,

I need to insert and update data from list of tables identified.

I am using dynamic SQL to pass the table name & column name (to be updated) dynamically using dynamic sql as below

PROCEDURE update_column(
table_name IN VARCHAR2,
update_column IN VARCHAR2)
IS
cur INTEGER;
rows_processed INTEGER;
old_value VARCHAR2(50) := '31/12/2010 00:00:00';
new_value VARCHAR2(50) := '31/12/3000 23:59:59';

BEGIN
DBMS_OUTPUT.PUT_LINE(
'Table name: '||table_name||' Column: '||update_column);

cur:=DBMS_SQL.OPEN_CURSOR;

DBMS_OUTPUT.PUT_LINE(
'UPDATE '||table_name||
' SET '||update_column||'='||'31/12/3000 23:59:59'||
' WHERE '||
update_column||'=>'||'31/12/2010 00:00:00' ||
' AND 1=1');

dbms_sql.parse(cur,
'UPDATE '||table_name||
' set '||update_column||'='||TO_DATE('31/12/3000 23:59:59','DD/MM/YYYY HH24:MI:SS')||
' WHERE '||
update_column||'>='||TO_DATE('31/12/2010 00:00:00','DD/MM/YYYY HH24:MI:SS')
,dbms_sql.native);


rows_processed:=DBMS_SQL.EXECUTE(cur);
DBMS_SQL.CLOSE_CURSOR(cur);

END;

i.e. i want the column value to be updated as '31/12/3000 23:59:59' wherever the value is greater than or equal to '31/12/2010 00:00:00'. But it is getting updated as '31/12/2000' instead of '31/12/3000 23:59:59'.

Please help. I tried but didn't get anything.

Many Thanks,
Aditi
Re: Dynamic SQL [message #306492 is a reply to message #306489] Fri, 14 March 2008 08:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are you sure your udated column is of date datatype?
I bet it is varchar2.

Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Regards
Michel

[Updated on: Fri, 14 March 2008 08:50]

Report message to a moderator

Count function in dynamic sql [message #306494 is a reply to message #306489] Fri, 14 March 2008 08:52 Go to previous messageGo to next message
aditiC
Messages: 38
Registered: February 2006
Location: INDIA
Member
Hi All,


Before updating the values, i need to insert the count of same in a temporary table. As i have arnd 59 tables, i am passing the table name dynamically using dynamic sql as below:

PROCEDURE INSERT_TEMP_TABLE(table_name IN VARCHAR2,
update_column IN VARCHAR2)
IS
cur INTEGER;
rows_processed INTEGER;
v_temp_table VARCHAR2(50) := 'TEMPXXX';
v_date_cnt NUMBER;

BEGIN
cur:=DBMS_SQL.OPEN_CURSOR;

dbms_sql.parse(cur,'INSERT INTO' || v_temp_table || 'select' || 'table_name' || update_column || count(1) ||
'from' || table_name ||
'where' || update_column || '>=' || to_date('31/12/2010 00:00:00','dd/mm/yyyy hh24:mi:ss') ||
'GROUP BY' || update_column ,dbms_sql.native);
rows_processed:=DBMS_SQL.EXECUTE(cur);
END;

But i am getting error in dbms_sql.parse stmt (bold above) as Compilation errors for PACKAGE BODY T2.CASCADE_UPDATE

Error: PLS-00204: function or pseudo-column 'COUNT' may be used inside a SQL statement only
Please help me out.
Re: Count function in dynamic sql [message #306496 is a reply to message #306494] Fri, 14 March 2008 08:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Build your statement inside a string and display it before parsing it and you will see it is completly meaningless.

Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Regards
Michel
Re: Dynamic SQL [message #306497 is a reply to message #306492] Fri, 14 March 2008 08:56 Go to previous messageGo to next message
aditiC
Messages: 38
Registered: February 2006
Location: INDIA
Member
Hi Michel,

yes the column to be updated is of date datatype. so i am converting to to_date and then assigning.

When i tried to print the value of
TO_DATE('31/12/3000 23:59:59','DD/MM/YYYY HH24:MI:SS') it is giving as 31-DEC-00
Re: Count function in dynamic sql [message #306498 is a reply to message #306496] Fri, 14 March 2008 08:58 Go to previous messageGo to next message
aditiC
Messages: 38
Registered: February 2006
Location: INDIA
Member
Hi Michel,

Sorry i didn't get you. Please explain again.
Re: Dynamic SQL [message #306499 is a reply to message #306497] Fri, 14 March 2008 08:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And how do you know that 00 is 2000?
Use TO_CHAR!

Regards
Michel
Re: Count function in dynamic sql [message #306500 is a reply to message #306498] Fri, 14 March 2008 08:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
my_statement := 'INSERT INTO' || v_temp_table ||
 'select' || 'table_name' || update_column || count(1) ||
'from' || table_name ||
'where' || update_column || '>=' ||
 to_date('31/12/2010 00:00:00','dd/mm/yyyy hh24:mi:ss') ||
'GROUP BY' || update_column ;
dbms_output.put_line(my_statement);

Regards
Michel
Re: Dynamic SQL [message #306501 is a reply to message #306499] Fri, 14 March 2008 08:59 Go to previous messageGo to next message
aditiC
Messages: 38
Registered: February 2006
Location: INDIA
Member
I checked the value in table it got updated as '31/12/2000'
Re: Dynamic SQL [message #306503 is a reply to message #306501] Fri, 14 March 2008 09:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Prove it.
Copy and paste the full session.
Post a test case we can reproduce.

Regards
Michel

[Updated on: Fri, 14 March 2008 09:01]

Report message to a moderator

Re: Dynamic SQL [message #306508 is a reply to message #306501] Fri, 14 March 2008 09:11 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Are you sure what you said ? But out of curiosity why 3000 and why not 9999 Smile
SQL> select TO_DATE('31/12/3000 23:59:59','DD/MM/YYYY HH24:MI:SS') from dual;

TO_DATE('
---------
31-DEC-00

SQL> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';

Session altered.

SQL>  select TO_DATE('31/12/3000 23:59:59','DD/MM/YYYY HH24:MI:SS') from dual;

TO_DATE('31/12/30002
--------------------
31-dec-3000 23:59:59

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE    10.2.0.2.0      Production
TNS for HPUX: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production

Regards

Raj
Re: Dynamic SQL [message #306510 is a reply to message #306508] Fri, 14 March 2008 09:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But out of curiosity why 3000 and why not 9999

Anyway it is a bad idea that will fool the optimizer.

Regards
Michel
Re: Count function in dynamic sql [message #306511 is a reply to message #306500] Fri, 14 March 2008 09:15 Go to previous messageGo to next message
aditiC
Messages: 38
Registered: February 2006
Location: INDIA
Member
Now i have corrected the stmt as

my_statement := ' INSERT INTO ' || v_temp_table ||
' select ' || 'table_name' || ',' || update_column || ''' || count(1) ||
' from ' || table_name ||
' where ' || update_column || '>=' ||
to_date('31/12/2010 00:00:00','dd/mm/yyyy hh24:mi:ss') ||
' GROUP BY ' || update_column ;
dbms_output.put_line(my_statement);

but still i am not able to use count function, getting error
Compilation errors for PACKAGE BODY T2.CASCADE_UPDATE

Error: PLS-00204: function or pseudo-column 'COUNT' may be used inside a SQL statement only
Line: 66
Text: ' select ' || 'table_name' || ',' || update_column || ',' || count(1) ||

Please help.
Re: Count function in dynamic sql [message #306512 is a reply to message #306494] Fri, 14 March 2008 09:18 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
>Now i have corrected the stmt as
If above statement was actually true, you would not be getting any error.

I suggest you give up on Oracle & focus on Access.
Re: Dynamic SQL [message #306513 is a reply to message #306510] Fri, 14 March 2008 09:20 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
You missed my point Razz

Regards

Raj
Re: Dynamic SQL [message #306514 is a reply to message #306510] Fri, 14 March 2008 09:20 Go to previous messageGo to next message
aditiC
Messages: 38
Registered: February 2006
Location: INDIA
Member
Hi Raj,

In my case, before executing the alter session command itself


SQL> select TO_DATE('31/12/3000 23:59:59','DD/MM/YYYY HH24:MI:SS') from dual;

TO_DATE('31/12/300023:59:59','
------------------------------
31/12/3000 23:59:59

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for HPUX: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

Please suggest something.
Re: Dynamic SQL [message #306515 is a reply to message #306514] Fri, 14 March 2008 09:21 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Post like I did.

Regards

Raj

Re: Dynamic SQL [message #306516 is a reply to message #306515] Fri, 14 March 2008 09:24 Go to previous messageGo to next message
aditiC
Messages: 38
Registered: February 2006
Location: INDIA
Member
Hi Raj,

SQL> select TO_DATE('31/12/3000 23:59:59','DD/MM/YYYY HH24:MI:SS') from dual;

TO_DATE('31/12/300023:59:59','
------------------------------
31/12/3000 23:59:59

SQL> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';

Session altered

SQL> select TO_DATE('31/12/3000 23:59:59','DD/MM/YYYY HH24:MI:SS') from dual;

TO_DATE('31/12/300023:59:59','
------------------------------
31/12/3000 23:59:59

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for HPUX: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

SQL>
Re: Dynamic SQL [message #306517 is a reply to message #306499] Fri, 14 March 2008 09:25 Go to previous messageGo to next message
aditiC
Messages: 38
Registered: February 2006
Location: INDIA
Member
Michel,

Please tell me the stmt to_char??
Re: Dynamic SQL [message #306523 is a reply to message #306517] Fri, 14 March 2008 10:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Instead of using implicit conversion, use explicit conversion.
TO_CHAR is the opposite of TO_DATE.

Regards
Michel
Re: Count function in dynamic sql [message #306524 is a reply to message #306511] Fri, 14 March 2008 10:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Format your post
Use SQL*Plus and post your session.
I don't understand if you have an error at compile time or at execution time.

Regadrs
Michel
Re: Count function in dynamic sql [message #306544 is a reply to message #306511] Fri, 14 March 2008 11:16 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
This should be
my_statement := ' INSERT INTO ' || v_temp_table ||
' select ' || 'table_name' || ',' || update_column || ' count(*) from ' || table_name ||
' where ' || update_column || '>=' ||
to_date('31/12/2010 00:00:00','dd/mm/yyyy hh24:mi:ss') ||
' GROUP BY ' || update_column ;
dbms_output.put_line(my_statement);

Above statement will work only when v_temp_table is having only two columns of which first column should be a character field and second column got to be a number. But what I am not able to understand why you are using dbms_sql to run it.

You are getting the mentioned error because you have not included count(1) as a string instead it is been used as an expression. I personally wouldn't advise count(1) because it carries not value. Either use count(*) or count(<column_name>) where column_name corresponds to a column in your database table.

I honestly suggest you to read the sql refence manual.

Regards

Raj

Re: Count function in dynamic sql [message #306672 is a reply to message #306494] Sat, 15 March 2008 12:20 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
aditiC try this:

declare
   sql_v varchar2(32000);
begin
   sql_v := 'your sql here';
   raise_application_error(-20999,sql_v);
end;
/


Gee, why are we raising an error? Cause we want to see the actualy sql being sent to the database that is why.

Your sql has some syntax error. Hard to tell what it is though if we can't see it. Once you see the sql you should be able to figure out what is wrong with it and recode your sql builder.

Good luck, Kevin
Re: Dynamic SQL [message #306833 is a reply to message #306523] Mon, 17 March 2008 01:18 Go to previous messageGo to next message
aditiC
Messages: 38
Registered: February 2006
Location: INDIA
Member
Hi Michel,

I still didn't get using to_char. Please help.
Re: Dynamic SQL [message #306843 is a reply to message #306833] Mon, 17 March 2008 01:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand where is your problem in using TO_CHAR, precise your question.

Regards
Michel
Re: Dynamic SQL [message #306872 is a reply to message #306843] Mon, 17 March 2008 02:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Format your post
2/ Put your generated query in a string and display it as we can see which one it is

Regadrs
Michel
Re: Dynamic SQL [message #306875 is a reply to message #306872] Mon, 17 March 2008 02:43 Go to previous messageGo to next message
aditiC
Messages: 38
Registered: February 2006
Location: INDIA
Member
Hi Michel,

I tried like
to_char('31/12/3000 23:59:59','dd/mm/yyyy hh24:mi:ss')
but getting error as too many declarations of to_char match this call.

I am using dynamic sql as below

dbms_sql.parse(cur,
'UPDATE '||table_name||
' set '||update_column||'='||TO_DATE('31/12/3000 :59:59','DD/MM/YYYY HH24:MI:SS')||
' WHERE '||
update_column||'>='||TO_DATE('31/12/2010 00:00:00','DD/MM/YYYY HH24:MI:SS'),dbms_sql.native);

i want date to be updated as '31/12/3000 23:59:59' but it is getting updated as '31/12/2000'.

a individual update works fine as below

update <tablename>
set <colname> =to_date('31/12/3000 23:59:59','DD/MM/YYYY HH24:MI:SS')
where <colname> = to_date('31/12/2010 00:00:00','DD/MM/YYYY HH24:MI:SS')
here the date is getting updated correctly as '31/12/3000 23:59:59'

I am not getting then what is the problem with my dynamic sql script.

Please help me out, it s very urgent.
Re: Dynamic SQL [message #306882 is a reply to message #306875] Mon, 17 March 2008 02:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Put your "update" statement in a string and DISPLAY it.

AND FORMAT YOUR POST.
This is my last answer until you format.

Regards
Michel
Re: Dynamic SQL [message #306903 is a reply to message #306882] Mon, 17 March 2008 03:55 Go to previous messageGo to next message
aditiC
Messages: 38
Registered: February 2006
Location: INDIA
Member
Hi Michel,

I tried printing the update statement.

I got output as below:

UPDATE AFR_PROD_KONTO
set GYLDIG_TIL_TIDSP=31-DEC-00
WHERE GYLDIG_TIL_TIDSP>=31-DEC-10;

i.e. the date is being updated as '31-DEC-00' instead of '31/12/3000 23:59:59'.


Thanks,
Aditi

Re: Dynamic SQL [message #306906 is a reply to message #306903] Mon, 17 March 2008 04:11 Go to previous message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Now change your statement and put the "TO_DATE..." inside the string and not outside: "'= to_date...'" and not "'= '||to_date"
Take care of number of '

Regards
Michel
Previous Topic: Number to Bit Function
Next Topic: Names separated by "|" in a single line to individual names
Goto Forum:
  


Current Time: Tue Dec 06 08:52:05 CST 2016

Total time taken to generate the page: 0.22058 seconds