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

SQL & PL/SQL :: Execute Large (clob) Dynamic Sql By DBMS_SQL?

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

Execute Dynamic SQL Using Both Execute Immediate And Ref Cursor

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

Execute Immediate To Dynamically Build A Trigger?

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

SQL & PL/SQL :: Execute Immediate Doesn't Like Dynamic String

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

SQL & PL/SQL :: Execute Dynamic Query Stored In Table?

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

SQL & PL/SQL :: How To Execute Dynamic Query In Procedure With Special Characters

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

SQL & PL/SQL :: Convert Update Query To Dynamic To Be Executed From Execute Immediate Statement

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

SQL & PL/SQL :: Querying Through Dynamic Database Links Using DBMS_SQL

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

SQL & PL/SQL :: How Does DBMS_SQL Differ From Native Dynamic Code

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

SQL & PL/SQL :: Find Final Text Of Dynamic Query Created By Dbms_sql?

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

Ora-06502 - Create Generate Procedure That Create Dynamic Procedure Through DBMS_SQL

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

Execute Immediate In Trigger

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

Execute Sql File From Pl / Sql?

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

SQL & PL/SQL :: Execute Immediate With DBLink

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

SQL & PL/SQL :: How Many Times Do The Inner Execute

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

SQL & PL/SQL :: FORALL With Execute IMMEDIATe

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

SQL & PL/SQL :: Error In Execute Immediate?

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

SQL & PL/SQL :: Specifying INTO Inside Execute Immediate?

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

PL/SQL :: How To Replace Execute Immediate In 11g

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

Execute Set Of Ddl Scripts In Pl/sql?

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

SQL & PL/SQL :: Execute Immediate Instead Of All 16 Conditions?

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

Execute Application Via Trigger?

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

Execute UPDATE From Field That May Have More Than Row

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

SQL & PL/SQL :: Execute Immediate Command Error

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

SQL & PL/SQL :: Getting Error When Using Clob In Execute Immediate

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

SQL & PL/SQL :: Execute Immediate Inside A WHILE Loop

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

SQL & PL/SQL :: Create A Procedure Using EXECUTE IMMEDIATE?

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

SQL & PL/SQL :: Execute KSH File From Oracle 8i?

Jul 4, 2011

calling .ksh file from Oracle8i Pl/Sql code.We have Unix and Oracle on same server.

View 39 Replies View Related

SQL & PL/SQL :: How To Use VArray In Using Clause Of Execute Immediate

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







Copyrights 2005-15 www.BigResource.com, All rights reserved