SQL & PL/SQL :: Using Dbms_sql.execute To Build / Execute Dynamic Sql?
Oct 23, 2013
I'm working with old code that uses dbms_sql.execute to build/execute dynamic sql. In our case, the user can select varying columns(I think up to 20) with different where conditions as needed.
After building the sql, here's an example
WITH ph AS
(SELECT ph.* FROM po_header ph WHERE 1 = 2),
pf AS
(SELECT DISTINCT pf.order_id, pf.fund
FROM po_fau pf, ph
WHERE 1 = 1
AND ph.order_id = pf.order_id
[code]....
Where table records for
po_header = ~567746
po_fau = ~2153570
and PK "order_id" is a NUMBER(10) not null and a snippet of the code looks like
nDDL_Cursor := dbms_sql.open_cursor;
dbms_sql.parse(nDDL_Cursor, sSQLStr, 2);
FOR x IN 1 .. nCols LOOP
sCols(x) := '';
dbms_sql.define_column(nDDL_Cursor, x, sCols(x), 100);
END LOOP;
nError := dbms_sql.execute(nDDL_cursor);
why when the "execute" statement is fired off the elapsed time takes ~4.5 seconds but If I change "1 = 1" above to "1 = 2" it takes ~.2 seconds. If I run the above query interactively it takes ~.2 seconds. Shouldn't the above query when joining
ph.order_id = pf.order_id
return zero rows back instantly or does the "dbms_sql_execute" do some other type of parsing internally that takes cpu time.
View 14 Replies
ADVERTISEMENT
Aug 2, 2011
is it possible to execute large(clob) dynamic sql by DBMS_SQL .Is there any restriction like length ...
View 4 Replies
View Related
Jan 8, 2009
We can execute dynamic sql using both execute immediate and ref cursor..But what is the difference between the two and performance-wise which is better?
View 5 Replies
View Related
Apr 25, 2011
We have developed a code which builds a trigger on a atble dynamically at run time usinf Dynamic SQL command Execute immediate.
create or replace
PROCEDURE GENERATE_TRIGGER
(
P_TNAME IN VARCHAR2
[Code].....
I am not usre if the error is due to insufficient privileges as we are able to build tabl;e in the system dynamically using the above mentioned command.
View 1 Replies
View Related
Feb 29, 2012
why is this procedure with EXECUTE IMMEDIATE not working for me, when I pass a dynamic string to it.
this is the proc:
CREATE OR REPLACE FUNCTION MIGRATION.execute_sql (pSQL varchar2) RETURN varchar2 IS
vResult Varchar(200);
BEGIN
vResult := null;
[code]......
and when I invoke it like this it's fine:
select execute_sql ('select sysdate from dual') test from dual. However, when I try something like this, it won't work:
select execute_sql ('select max(al_code) from alert_log where al_user= '||user) test_sql from dual
the error message I get is:
ORA-00904: "MIGRATION": invalid identifier
ORA-06512: at "MIGRATION.EXECUTE_SQL", line 26
View 11 Replies
View Related
May 27, 2011
I want to execute a dynamic query which is stored in a Table.
Output of that query should be stored in database server.
Is there any way i can create a dynamic procedure? I have created a sample code but issue is i cannot make the below data type dynamic as per the query.
en com_fund_info_m%ROWTYPE;
CREATE TABLE TEMP (SQLSTATEMENT VARCHAR2(100))
DECLARE
TYPE r_cursor IS REF CURSOR;
c_emp r_cursor;
en com_fund_info_m%ROWTYPE;
[code].....
View 3 Replies
View Related
Dec 17, 2012
I have the following procedure body in a package.
PROCEDURE getrecordsForinspection(i_table_name in varchar2, i_thread_id in varchar2, i_max_count in number default null, o_results out sys_refcursor)
AS
v_sql varchar2(1000):= null;
begin
v_sql := 'update '||'i_table_name||' set status = '||'''IN_PROCESS-'||i_thread_id||''''||' Where final_status = '||''''STATUS_ACCEPTED'''||' and ('||i_max_count||' is null or rownum <= '||i_max_count||');';
EXECUTE IMMEDIATE(v_sql);
commit;
end;
when I execute the above procedure it gives the following error.
ORA-00911: invalid character
cause: Identifiers may not start with any ASCII characters other than letters and numbers.$#_ are also allowed after the first character. Identifiers enclosed by double quotes may contain any character other than a double quote. Alternative quotes(q'#....#') can not use spaces, tabs, or carriage returns as delimiters. For all other contexts, consult the SQL language reference Manual.
I think dynamic sql is not executed because of the pipe character in the sql statement.
View 2 Replies
View Related
Oct 25, 2010
I want to convert the below SQL to a dynamic sql to be executed from execute immediate statement.
UPDATE transaction SET loannum = lpad(loannum,12,'0')
View 15 Replies
View Related
Nov 14, 2011
I am using SQL Developer.I am self-teaching myself PL/SQL. What I am trying accomplish is to run a select query across many database links at runtime and to insert that query onto a local table. So far, I have only gotten as far as querying the database link names. I am stuck at where my variable calls the database link name. It does not recognize the database link name, and I can't quite grasp the reason why. Below is the first part of my script which does retrieves the column values no problem:
001 SET SERVEROUTPUT ON
002 DECLARE
003 db_link_varVARCHAR2(30);
004 source_cursorINTEGER;
005 destination_cursorINTEGER;
006 src_csrINTEGER;
007 dst_csrINTEGER;
008 rundate_varDATE;
009 instance_name_var VARCHAR2(30);
[code]....
If I break the script down to the bare select query, it will query absolutely fine.If I run the script in its entirety, it will not pick up the variable dbl as a dynamic database link. 02019. 00000 - "connection description for remote database not found"
If I comment out line 031 and prevent the looping of querying of database links (which will only fetch the first value of db_link from dba_db_links), the script will complete and I will have a row inserted into my local table.
I am suspecting it's the looping that is incorrect but I understand it is not going to do anything beyond line 059.
View 8 Replies
View Related
Aug 13, 2012
Let me the difference between the DBMS_SQL and Execute immediate .
*) Which one is faster ?
*) Where to use DBMS_SQL and Execute immediate (correct usage of both)?
View 4 Replies
View Related
Jul 4, 2012
I am using dbms_sql for creation and execution of dynamic query. But after execution,the results returned are not correct. Just wanted to know is there some way by which I can find the final text of dynamic query created by dbms_sql?
View 14 Replies
View Related
Mar 31, 2004
ORA-06502...I have database on oracle 9i on Solaris 9. I create a generate procedure that create dynamic procedure through DBMS_SQL. On this database I got the ORA-06502 error. When I tried to run the same procedure on the same database on oracle 8i on NT this work fine.
View 3 Replies
View Related
Apr 17, 2013
I am trying to use execute immediate in a trigger. See the below example
create table test_tbl(col1 number,col2 varchar2(10), col3 date);
create table test_tbl_log as select * from test_tbl where 1 = 2;
create or replace trigger test_trig
after insert or update or delete on test_tbl
for each row
declare
v_str varchar2(4000);
[code].......
insert into test_tbl values(3,'test',sysdate);
I got the Error : -1008->ORA-01008: not all variables bound
In the execute immediate statement if I change the v_using_str variable to :new.col1,:new.col2,:new.col3 trigger is running fine. But here I have to create trigger on all the tables in my schema and I dont want to list all the column names for each table manually and I am using a for loop to build the v_using_str string in my original code.
View 2 Replies
View Related
Aug 18, 2010
Need to execute @/sql file after checking an output of below sql.
SELECT COUNT(*) FROM all_directories WHERE DIRECTORY_NAME='RMANDUMP';
If directory exist then oonly we require to run sql file.
If there a way we can execute sql files depending on sql output.
View 4 Replies
View Related
Feb 23, 2011
I need to write several variations of this statement below to query a remote database, but am having trouble with the syntax.
execute immediate 'select count(*) from ' || v_tablename || '@dblink ' || into v_rowcount;
PLS-00103: Encountered the symbol "INTO"....
What's the correct way to format this kind of dynamic query?
View 4 Replies
View Related
Oct 2, 2010
I found this query in one of my stored procedures that updates a key for a value in a data table by reading the information from a master table.
The data table is: ITEM_INVENTORY
The master table is: MASTER_SOURCE_SYSTEM
UPDATE ITEM_INVENTORY I
SET I.SOURCE_SYSTEM_ID =
(SELECT NVL(M.SRC_SYS_ID,-100)
FROM MASTER_SOURCE_SYSTEM M
WHERE M.SRC_SYS_DESC(+) = I.SOURCE_SYSTEM_CODE )
WHERE ORG_CODE = 'TNXC'
AND EXISTS (SELECT 1 FROM MASTER_SOURCE_SYSTEM M
WHERE M.SRC_SYS_DESC(+) = I.SOURCE_SYSTEM_CODE )
The situation here is that:
1. There are about 15000 rows that match ORG_CODE = 'TNXC'.
2. The SOURCE_SYSTEM_CODE is same for all the 15000 rows and there is a matching entry for it in the MASTER_SOURCE_SYSTEM table.
My question is: Do both the inner select statements execute 15000 times?
The statement executes within a second and updates 15000 rows. How is this made possible?
View 10 Replies
View Related
Jan 11, 2006
I am trying the following
FORALL l_loop_cntr IN 1..l_count
EXECUTE IMMEDIATE ' INSERT INTO ' ||c_table || ' VALUES l_NE_BILL_REPORTS_table (:1) ' USING l_loop_cntr;
my table name is dynamic.
but it gives the error Compilation errors for PROCEDURE PL_OWNER.TEST_BULK
Error: PLS-00435: DML statement without BULK In-BIND cannot be used inside FORALL
Line: 27
Text: EXECUTE IMMEDIATE ' INSERT INTO ' ||c_table || ' VALUES l_NE_BILL_REPORTS_table (:1) ' USING l_loop_cntr;
View 4 Replies
View Related
May 10, 2013
I have attached my Code and log records, here while calling Execute Immediate the program throws an error.
View 4 Replies
View Related
Jun 29, 2011
The following code works
set serveroutput on
declare
a int;
begin
execute immediate 'select employee_id from employees where first_name=:ab' into a using 'Donald' ;
dbms_output.put_line(a);
end;
but this one doesn't
set serveroutput on
declare
a int;
begin
execute immediate 'select employee_id into :1 from employees where first_name=:2' using a,'Donald';
dbms_output.put_line(a);
end;
Am I not allowed to specify a bind variable with an into clause inside execute immediate ?
View 9 Replies
View Related
Jan 18, 2013
creating package and i need not to use execute immediate. There are some dynamic build plsql's and sql's stored in global lists. Any other possibility to run them without execute immediate?
View 3 Replies
View Related
May 22, 2013
If I use DBMS_METADATA.GET_DDL and DBMS_METADATA.GET_GRANTED_DDL for a particular user I can get something like this:
CREATE USER "USERB" IDENTIFIED BY VALUES 'B6C9E444D14CDE5B' DEFAULT TABLESPACE "PROD_TBSP_03" TEMPORARY TABLESPACE "TEMP";
GRANT "SELECT_CATALOG_ROLE" TO "USERB";
GRANT "SCHEMA_ROLE" TO "USERB";
Now, how do I go executing this in pl/sql? The following would fail as execute immediate wouldn't run all of these at the same time:
DECLARE
v_str VARCHAR2(4000 BYTE);
BEGIN
v_str := 'CREATE USER "USERB" IDENTIFIED BY VALUES ''B6C9E444D14CDE5B'' DEFAULT TABLESPACE "PROD_TBSP_03" TEMPORARY TABLESPACE "TEMP";'
|| chr(10) || 'GRANT "SELECT_CATALOG_ROLE" TO "USERB";'
|| chr(10) || 'GRANT "SCHEMA_ROLE" TO "USERB";';
dbms_output.put_line(v_str);
EXECUTE IMMEDIATE v_str;
[code]....
View 8 Replies
View Related
Aug 21, 2013
I created a procedure with four in parameters and 1 out parameter is there in this i want to check if any parameters is null or any two parameters are null or any three parameters are null...like this i checked(16 conditions) for all combinations i put if conditions but can i use execute immediate instead of all 16 conditions?
View 2 Replies
View Related
Feb 6, 2007
I've been asked to look into ways I an execute an application with a trigger. I know I could set some database flag and have an application look at the flag and execute an application based on this, but my boss really doesn't wanna go down this route.
I was told I can use either C or java within the trigger somehow to execute an application (for example notepad.exe)
View 1 Replies
View Related
Jun 29, 2010
I am trying to execute an UPDATE from a field that may have more than row. I get ORA-01427: single-row subquery returns more than one row.
I need to set the careof column in owner table to hold the name (NAM) from the alt table if the alttype from alt is ATT. We could have more than one id and the careof names can be different.
This is my query:
UPDATE owner s
SET s.careof = (SELECT a.NAM
FROM alt a
WHERE a.taxyr = '2011'
AND a.alttype = 'ATT'
AND a.card in ('0')
[code]....
View 1 Replies
View Related
Oct 14, 2011
I am facing a problem regarding the execute immediate command. I have created a procedure as given below
SQL> set echo on ;
SQL> set serveroutput on;
SQL> declare
2 l_var varchar2(50);
3 sqlstring varchar2(3000);
4 begin
[code].......
In this procedure the execute immediate command shows error ( if i avoid exception).I have tried other syntax too of this command
but it is showing error only.
View 13 Replies
View Related
Oct 18, 2010
I m getting the following error when using the clob in execute immediate : 'ORA-22275: invalid LOB locator specified'
declare
v_final_output1 clob := empty_clob;
v_stmt varchar2(32000);
begin
select source into v_stmt from table t where t.id =123 ;
[code]....
Note that error comes after the execute immediate when i try to display the content of v_final_output1;
View 5 Replies
View Related
Jan 6, 2011
I have a dynamic query which i want to run till it return zero records.
I am using WHILE loop for that but it is giving compilation error:
The query is
execute immediate ' Delete from tbl_archive_trade_list
where deal_id in (
select deal_id from tbl_archive_trade_list where trade_id in (
select trade_id from ' || main_trade_group_table || ' where tradegroup_id in (
select tradegroup_id from ' || main_trade_group_table || ' a , tbl_archive_trade_list b
[Code]...
I want to run this Query in While loop till the above command return 0 records.
I tried giving the above statement inside WHILE loop but it is failing.
Without the WHILE loop the above statement works fine and executed properly.
View 5 Replies
View Related
Apr 14, 2013
I am trying to create a procedure using the EXECUTE IMMEDIATE. I have been having problems calling it from an anonymous block
My code
CREATE OR REPLACE PROCEDURE homework
(p_table_name VARCHAR2)
IS
v_department_id departments.department_id%TYPE;
BEGIN
EXECUTE IMMEDIATE
[code]....
I called the procedure from an anonymous block
BEGIN
homework('Employees');
END;
It gives me an error
ORA-00905: missing keyword
View 3 Replies
View Related
Jul 4, 2011
calling .ksh file from Oracle8i Pl/Sql code.We have Unix and Oracle on same server.
View 39 Replies
View Related
Sep 23, 2010
I'm trying to optimize an application running heavy updates/inserts/deletes, by having it using bind variables instead of "string queries".
The columns to be updated can vary (possibly from one column to all columns of a table), thus I have made som logic to build the query accordingly. My problem is now that I cant get the EXECUTE below to handle the VARRAY passed in the USING clause, it fails with "PLS-00457: expressions have to be of SQL types"
Environment:
create table table_x (a varchar2(10),b varchar2(10),c varchar2(10),d varchar2(10));
insert into table_x values('a','b','c','d');
commit;
Code, simplified with static number of columns:
declare
type v is varray(10) of varchar2(20);
v_values v:=v('A','B','c');
myupdate varchar2(2000);
begin
myupdate:='update table_x set a=:a, b=:b where c=:c';
dbms_output.put_line(myupdate);
execute immediate myupdate using v_values;
end;
I've understood that I cannot send TABLE type variables by USING, but this should be an VARRAY.
View 11 Replies
View Related