Home » SQL & PL/SQL » SQL & PL/SQL » Reg Dynamic Sql.
Reg Dynamic Sql. [message #254493] Thu, 26 July 2007 23:22 Go to next message
musomasu
Messages: 8
Registered: July 2007
Junior Member
Hi

I am trying to create the below procedure with dynamic sql.
and facing issue.

My requirement is to get the count of a column from a table for a particular value and update another table.

Procedure.

CREATE OR REPLACE PROCEDURE GET_COUNT(tname in varchar,COL IN VARCHAR) AS
v_rows integer;
qry_string varchar(2000);
BEGIN

qry_string:='select count(*) from '|| tname ||' where ' ||col || '=''3000'';';

DBMS_OUTPUT.PUT_LINE(QRY_STRING);

--EXECUTE IMMEDIATE QRY_STRING INTO V_ROWS;

--DBMS_OUTPUT.PUT_LINE(V_ROWS);

EXCEPTION
when others then
-- DBMS_SQL.CLOSE_CURSOR(v_cursor);
DBMS_OUTPUT.PUT_LINE(V_ROWS);
raise_application_error(-20000, 'Unknown Exception Raised: '||sqlcode||' '||sqlerrm);
END;
/


PLS-00357: Table,View Or Sequence reference 'EMP' not allowed in this context
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


Elapsed: 00:00:00.01

Please advice;

Thanks
Musomasu
Re: Reg Dynamic Sql. [message #254508 is a reply to message #254493] Fri, 27 July 2007 00:20 Go to previous message
shanthkumaar
Messages: 156
Registered: February 2007
Location: india,chennai
Senior Member

hi,

create or replace procedure test_ora(tname in varchar2,col in varchar2)
is
begin
declare
v_rows number;
query varchar2(256);
begin
query:='select count(*) from '|| tname||' where '|| col ||'='||''''||10000||'''';
execute immediate query into v_rows;
dbms_output.put_line(v_rows);
end;
end test_ora;
/


Procedure created.

SQL> exec test_ora('emps','salary');
1
/


regards,
shanth.

[Updated on: Fri, 27 July 2007 00:26]

Report message to a moderator

Previous Topic: inane title goes here.
Next Topic: how to send a blob field as email attachment
Goto Forum:
  


Current Time: Fri Dec 02 14:16:41 CST 2016

Total time taken to generate the page: 0.05507 seconds