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.
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?
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.
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;
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.
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:
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.
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?
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.
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.
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?
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 ?
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?
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;
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?
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)
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')
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.
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.
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.