tom,

in this following how do we do it.

declare

a number;
b varchar2(1) : 'c';
c varcha2(2) := 'XX';
d varchar2(2) := 'YY';
e varchar2(2);

begin

if a is null then
b := 'c';
else
b := 'd';
end if;

execute immediate 'select ' || b || ' from dual ' into e;

end;

i get an error invalid column name.

can't we refer dynamaically a variable. i know in forms we do it using name_in. is there any way to do this.

nagaraju

i run it.

declare
2
3 a number;
4 b varchar2(1) := 'c';
5 c varchar2(2) := 'XX';
6 d varchar2(2) := 'YY';
7 e varchar2(2);
8
9 begin
10
11 if a is null then
12 b := 'c';
13 else
14 b := 'd';
15 end if;
16 execute immediate 'select :x from dual' INTO e USING b;
17 dbms_output.put_line(e);
18 end;
19 /
c

PL/SQL procedure successfully completed

i need the value of the variable c i.e. 'XX'..

again like you refer int FORMS 4.5 ONWARDS NAME_IN OR COPY functions

I have table t which contains tablenames.

I need to check if value 'xyz' EXISTS IN a column which exists in all these tables. The column name in which value 'xyz' exists in all these tables is CHECK_VERIFY.

What is the best way to select all these table names out of
table t and verify if such values exists in column CHECK_VERIFY.

I cant do it with a regular cursor loop style as I cannot use a cursor value as table name in the from clause..

How should I do it using dynamic sql..

Thanks

I tried to use your code in Reports . But it complained.
can execute immediate be used in oracle reports and oracle forms

Does sqlplus, reports, forms have an inbuilt pl/sql engine.
I could not use execute immediate from reports, so it obvious that it does not have pl/sql engine.

Does sqlplus have a pl/sql engine or does forms does.

So is it like that, the clients have only sql engines so that sql is checked for syntax, and when it is sent to the database, the sql and pl/sql are separated and processed by the respective engines.

Please clarify. This understanding will help a lot.

and

usually there is a database engine containing both sql and pl/sql parser, and the clients will contain only the sql parser. I'm I right?


(Do you cover the sqlengine and plsql engine , and the database engine stuff anywhere in your book.)

Tom

1.so can we conclude that the pl/sql engine in oracle forms and reports 6i is not 8.1 , as such we cannot use execute immediate in them..
2.Well, then what is the version of pl/sql engine in oracle forms and reports 6i?

3.execute immediate is a plsql "verb", -- do you mean that execute immediate is a plsql construct when you say 'verb'

Is this PL/SQL Engine just a compiler of PL/SQL code

Hi Tom 

There is little problem for you...

when I execute the below procedure it generates a dynamic insert and executed but givig following error....

SQL> EXEC SP_COPYAUDITDATA_V1
DELETE
INSERT INTO CODE_AUDIT VALUES( '801','TEST','TEST
TEST','','AUD','','1','23-jul-02','7832','D',SEQ_CODE_AUDIT.NEXTVAL);
BEGIN SP_COPYAUDITDATA_V1; END;

*
ERROR at line 1:
ORA-00911: invalid character
ORA-06512: at "FPIMLVER103.SP_COPYAUDITDATA_V1", line 84
ORA-06512: at line 1

but when I copied the same insert statement and and run it explicitly from
sql*plus it works fine as u can see below..

SQL> INSERT INTO CODE_AUDIT VALUES(
'801','TEST','TEST
TEST','','AUD','','1','23-jul-02','7832','D',SEQ_CODE_AUDIT.NEXTVAL);

Pls. tell me where is the problem...

I'm sending each value with in quotes because avoiding to identify the char
and number becasue oracle implicitly treats the number in single quotes as
numeric value
and except number everything should be in quotes.

Thanks. 

Hi Tom
How r u ??? I'm fine and hope for the same...
one problem for you..

declare
v varchar2(100):='name,roll,class,marks'; --field names of a table
begin
open for cur 'select ' || v || ' ,city,pin from table';
loop
fetch...........
processing....
end loop;
close cur;

above is just sample not actual code..in which dynamic select is firing and variabke "v" contains few the field names and few are hardcoded.

my prob is that ...if we don't know that what fields, variable "v" will have next time then how can we fetch the values from dynamic cursor???????

Thanks.

HI, Tom
I write the stored procedure like the following to get value based on the column_name, but it get me the actually column name and does not the values of that columns I want!
How can I modify it? please give me some suggestions, thanks.

DECLARE
v_val_1 VARCHAR2(60);
v_val_2 number := 92;
BEGIN
FOR rec IN (SELECT column_name
FROM user_tab_columns
WHERE table_name = 'AC_PR'
AND column_name <> 'RUN_NO')
LOOP
execute immediate
'SELECT :X
FROM ac_pr
WHERE run_no = :Y'
INTO v_val_1
USING rec.column_name,
v_val_2 ;
DBMS_OUTPUT.PUT_LINE(v_val_1);
END LOOP;
END;
/

Hi Tom,
The previous developer in my project has written the following stored procedure to execute dynamic sql.

CREATE OR REPLACE PROCEDURE EXEC_SQL(STRING IN VARCHAR2) AS
/* Local Variable Declaration Block */
CURSOR_NAME INTEGER;
RET INTEGER;
/* end of MICROSOFTDTPROPERTIES */

BEGIN -- Start Begin Block 1
CURSOR_NAME := DBMS_SQL.OPEN_CURSOR;
BEGIN -- Start Begin Block 2
DBMS_SQL.PARSE(CURSOR_NAME,
STRING, DBMS_SQL.native);
RET := DBMS_SQL.EXECUTE(CURSOR_NAME);
EXCEPTION -- Exception for Select Statement
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(CURSOR_NAME);
RAISE;
END -- End of Begin Block 2
; DBMS_SQL.CLOSE_CURSOR(CURSOR_NAME);
END; -- End of Procedure

I have written the following stored procedure for achieving the same result.

Create or replace procedure SP_ExecuteSQL(in_String IN varchar2)
Is
begin
Execute Immediate in_String;
Exception
When Others Then
dbms_output.put_line(SQLERRM);
end;


Could you please tell me what is the difference between the two?

Regards,
Abhijit

Hi Thomas,
Thanks for getting that across.
I have now dropped both of those stored procedures as advised by you.

I am using EXECUTE IMMEDIATE along with Bind Variables as follows.

sSQL = "EXECUTE IMMEDIATE " & _
"'Insert Into Poets(PID, FNAME, LNAME, COUNTRY) values(:w,:x,:y,:z)' " & _
" USING '" & txtPID.Text & "','" & txtFName.Text & "','" & _
txtLname.Text & "','" & txtCountry.Text & "';"

This SQL Statement is printed out as follows.
EXECUTE IMMEDIATE
'Insert Into Poets(PID, FNAME, LNAME, COUNTRY) values(:w,:x,:y,:z)' USING 'A7','Emily','Bronte','england'


My Poets Table is as follows
Create Table Poets
(
PID char(2),
FNAME varchar2(40),
LNAME varchar2(40),
COUNTRY varchar2(50)
)

Could you please tell me what am I doing wrong?

Cheers!
Abhijit

Hi Tom,
Thanks for your immediate response on EXECUTE IMMEDIATE.
I have figured out another way to do this apart from the link that you sent. Yes the code is in Visual Basic.

Here is my method.

sSQL = "BEGIN" & Chr(13)
sSQL = sSQL & "EXECUTE IMMEDIATE " & _
"'Insert Into Poets(PID, FNAME, LNAME, COUNTRY) values(:w,:x,:
y,:z)' " & _
" USING '" & txtPID.Text & "','" & txtFName.Text & "','" & _
txtLname.Text & "','" & txtCountry.Text & "';"
sSQL = sSQL & "END;"
mCn.Execute sSQL 'mCn is my connection object.

This works out as well. Is this binding variables?

Regards,
Abhijit

That has cleared up my other doubts as well.

Cheers!
Abhijit

Tom,
Hard to convince my developer colegue Visual-C that bind variable is a solution to improve performance. This is scenario: he has to call the SAME query about 100 times with distincts values on WHERE clause like 'select * from table_a WHERE b = 100' and 'select * from table_a WHERE b = 102' and so on.
I am thinking give up to convince him to use bind variable. He says there are many trouble to use this in his language and bla bla bla ...
I thought use stored procedure, but he refute saying "doesn't work here -- you don't know write any line on C program, it is different from your pl/sql and SQL [magic] *Plus ".

Tom, could you help with some piece of information?

Thanks alot!
Marcio

Tom,

I also have a person using VB and ADO that wants to inserts into my database. He set it up to use non binding SQL and I said no, especially since the insert on his side will be executed in a loop (1000 times per night into a wide table).

I can make him bind as in the google link or wondering if I created a procedure like the psudo code below. what effect on the shared pool and would you consider this binding before oracle? and a good or bad idea?

procedure insert_Row_from_bs_platform
(p1 varchar2, p2 varchar2)
is
begin
insert into table_name
(f1, f2) values
(to_date(p1,'mm/dd/yyyy HH:mi:ss am'), (p2) );
end;

I would put in a package and let him call that procedure (instead of the insert)...from ADO/VB w/text strings as inputs.

Would that be binding before the shared pool, how would this fit on your scale of doing things the "right" way?

Also wants to execute many that will fail for dup value...to let the few new records captured in (and avoid dups as there is no good ID in the other BS system)...should I force him to program a mechanism for the bulk of that on his side...or let the dup_val_on_index error in Oracle be his tool.

Other suggestions?

Thanks,
Meyer

That makes sense...Thanks Tom.

Hi Tom,
Source tables data needs to be inserted into target tables (the definitions of target tables are same as source tables)after converting some of the input values (standard codes)to the lookup values. The lookup table contains table name(s) and column name(s)along with input value and lookup value. If the lookup value is found, substitute the input value with lookup value, insert row in target table else insert in reject table.
Thanks

There are plenty of tables to be loaded with weekly run. The source tables (directly loaded from raw data)contain some code columnns +others with source_system_code. For example, for codes, employer table has employer status, employer type columns, and policy has policy type, policy category etc. The lookup table is built to standardise the codes, as data is coming from disparate systems. The lookup table contains source_system, table_name, column_name, date_effective from, date_effective_to, source code, standard code, columns. If for one system (SS1) the employer status 'A' means Active, other (SS2) may have 'R' (Recent) as Active. So, the table is built as
SS1, employer (table name), '01-JAN-2000','31-DEC-999', employer status (column name), A, C (current);
SS2,.........................,R, C. The target tables have referential integrity with standard codes tables with description like, table employer status table has status -> A, Description-> Active.
I was asking about building a generic script passing table name as an argument, get the lookup code columns, go to lookup table, return standard code, and finally insert into target the record with standard code. If the source code is not found then reject the record in rejects table.
Is it possible to do through views without the reject part? as I have a generic script which loads data in target, and puts records in rejects table if FK of codes tables is violated. In this way, I can use view as input to generic loading script. If possible, could you please give an example?
Thanks

Tom,
Since I did't know all of the outputs at compile time, so I used DBMS_SQL to insert from source to target tables. The script works fine, but now the source tables columns having codes need to lookup in the lookup tables before insert into target tables. As DBMS_SQL's API's DEFINE_COLUMN and COLUMN_VALUE works with position, how to I lookup in lookup table i.e., how to determine how many lookup aliases (lkp1, lkp2....) are needed to dynamically passed table?

subject: i need to refer the value of the variable dynamically not thename March 06, 2002
Reviewer: nagaraju

*I thought it might be helpful to them...

declare
a number;
b varchar2(10) := 'c';
c varchar2(10) := 'XX';
d varchar2(10) := 'YY';
e varchar2(10);
abc varchar2(240);
begin
if a is null then
b := '''c''';
else
b := 'd';
end if;
abc := 'select ' || b || ' from dual ';
execute immediate abc into e;
dbms_output.put_line(e);
end;
/

Tom,
I have the following question about update query:

we have a loan table: state_cd,
user_id,
status_cd,
....

user_state_served: user_id,
state_cd,
last_asgnmt_dt

We want to distribute the loans equally to employees authorized to work in the state.
I do that by updating last_asgnmt_dt = sysdate and taking the user_id
with min(last_asgnmt_dt) for next assignment.

However, after the initial assignment the workload may be different between employees
based on number of loans processed in a state.
How do I take the average number of loans in a state and divide equally among employees

for example for the state of TX, there are 400 loans.
There 4 workers in that state. This the distribution right now:

emp 1: 150
emp2 : 50
emp3: 75
emp 4 : 0

unassigned: 125.

I want to equally divide all the active loans among 4 employees.

Greetings,

Is it possible to use prepared statements (PS)on an dynamic SQL generated by a front end web app ?

Our head Java programmer told me that only static queries can be PS'ed.

If PS'ed dynamic sql is possible can you provide a simple example ?

Thanks.

Respectfully yours,
Alvin

create or replace package test1 as
  procedure p_public1; 
  procedure p_public2; 
end;
/

create or replace package body test1 as
  procedure p_private as 
  begin
    null;
  end;

  procedure p_public1 as 
  begin
    p_private;
  end;

  procedure p_public2 as 
  begin
    execute immediate 'begin p_private; end;';
  end;
end;
/


SQL> exec test1.p_public1;

PL/SQL procedure successfully completed.

SQL> exec test1.p_public2;
BEGIN test1.p_public2; END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'P_PRIVATE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
ORA-06512: at "PAGO_V20SUNP2.TEST1", line 14
ORA-06512: at line 1


How could I invoke a private procedure using Dynamic SQL?

Thanks. 

Tom,

Let's assume that we have an input field that can accept multiple values from user.

Corresponding SQL statement would be

Select * from TABLENAME WHERE COL_VAL IN (val1,val2,val3)

How can we do this in Prepared statement?

Do we need to create three bind variables in statement?

Regards,
Divakar

Hi

I amd trying to write a pl/sql to find invalid synonyms as follows

declare
x number;
begin
for i in (select synonym_name from user_synonyms)
loop
execute immediate 'select 1 from ' || i.synonym_name || ' where rownum = 1' into x;
if x is null then
dbms_output.put_line(i.synonym_name);
end if;
end loop;
end;
/

it seems like X is never null which cannot be true because I have invalid synonyms. How so? Why x is never null?

because, it is throwing an ORA 980 when the synonym is bad:

ops$tkyte@ORA920> create synonym totally_bogus for nothing_up_my_sleeve;

Synonym created.

ops$tkyte@ORA920> 
ops$tkyte@ORA920> declare
  2    x number;
  3  begin
  4     for i in (select synonym_name from user_synonyms)
  5     loop
  6           execute immediate 'select 1 from ' || i.synonym_name || ' where rownum = 1' into x;
  7           if x is null then
  8              dbms_output.put_line(i.synonym_name);
  9           end if;
 10     end loop;
 11  end;
 12  /
declare
*
ERROR at line 1:
ORA-00980: synonym translation is no longer valid
ORA-06512: at line 6


It never gets to the code "if x is null".  even if it did -- X would just have the LAST value of whatever was in it -- not null.

ops$tkyte@ORA920> create synonym totally_bogus for nothing_up_my_sleeve;

Synonym created.

ops$tkyte@ORA920> 
ops$tkyte@ORA920> declare
  2      synonym_bogus EXCEPTION;
  3          pragma exception_init( synonym_bogus, -980 );
  4  begin
  5     for i in (select synonym_name from user_synonyms)
  6     loop
  7     null;
  8               begin
  9                      execute immediate
 10                        'create or replace view v 
                              as select * from ' || i.synonym_name;
 11                   exception
 12                      when synonym_bogus then
 13                              dbms_output.put_line( i.synonym_name );
 14                   end;
 15     end loop;
 16  end;
 17  /
TOTALLY_BOGUS

PL/SQL procedure successfully completed.


is one technique.

 

tried this

set serverout on

select table_name from user_tables where table_name = 'EMP';

TABLE_NAME
------------------------------
EMP

select table_name from user_tables where table_name = 'MP';

no rows selected

declare
x number;
begin
for i in (select table_name from user_tables where table_name = 'MP')
loop
execute immediate 'select 1 from ' || i.table_name || '
where rownum = 1' into x;
if x is null then
dbms_output.put_line(i.table_name);
end if;
end loop;
end;
/

PL/SQL procedure successfully completed.

We should have seen table_name above

declare
x number;
begin
for i in (select table_name from user_tables where table_name = 'EMP')
loop
execute immediate 'select 1 from ' || i.table_name || '
where rownum = 1' into x;
if x is null then
dbms_output.put_line(i.table_name);
end if;
end loop;
end;
/

Expected behaviour

It seems that X is never null?

Tom:

Thanks much for all the help.

I have a question for you. The following code builds the table when it is called with no argument at all. Attempt to build the objects in other schemas failed but the proc successfully completed. What am I doing wrong here? Thanks.

CREATE OR REPLACE PROCEDURE BUILD_SCHEMA (p_schema_name IN VARCHAR2) AS

p_schema VARCHAR2(64);
p_sql VARCHAR2(2000);
results VARCHAR2(1000);

BEGIN

IF p_schema_name IS NOT NULL THEN
p_schema := p_schema_name || '.';
END IF;

p_sql := 'CREATE TABLE ' || p_schema || 'PERSONZ (
id NUMBER(*) PRIMARY KEY,
ts DATE NOT NULL,
last_name VARCHAR2(40) NOT NULL,
middle_name VARCHAR2(40),
first_name VARCHAR2(40) NOT NULL,
phone_num VARCHAR2(32) NOT NULL,
email_addr VARCHAR2(241),
street_addr_1 VARCHAR2(80),
street_addr_2 VARCHAR2(80),
city VARCHAR2(64),
state VARCHAR2(3),
zip VARCHAR2(16),
country VARCHAR2(3),
sel_security_ques VARCHAR2(32),
security_answer VARCHAR2(32)
) TABLESPACE xyz_ds';

EXECUTE IMMEDIATE p_sql INTO results;

COMMIT;

DBMS_OUTPUT.PUT_LINE(p_sql);
DBMS_OUTPUT.PUT_LINE(results);

EXECUTE IMMEDIATE 'CREATE SEQUENCE personz_seq';

EXCEPTION --Error handler
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE (results);
END;

we have to maintain data exchange via files between our oracle databases and customer systems.

several subsystems provide standardize interfaces to our internal systems for that purposes. they contain data structures for data to be received/sent as well as routines to process this data. incoming data is converted into the standardized internal formats via xml/xslt. on the output side xml/xsl serves for converting data into customer formats.

since these data structures reside on our internal systems that receive/send data they are *not* generic. but due to different customer needs we are forced to make the import routines more flexible (for export this is not a problem). for instance customers want to be able to exclude destination tables from beeing affected. in addition they want to configure whether rows should be imported (inserted/updated) as a whole or piecewice (process certain columns only).

to provide that scale of flexibility we are thinking of using dynamic SQL in conjunction with "normal" stored code: a stored PL/SQL routine implements the steps of the overall "workflow" - processing the data in the order it has to be done (due to target RI constraints).
every step uses its own anonymous PL/SQL block that gets intialized once per import and executed once per source row. each PL/SQL blocks contains a customized update and insert operation for a certain data structure. at runtime those PL/SQL blocks get invoked via cursors from within the main routine. in case of an update the rowid of the row to be updated is provided by the caller. the PL/SQL blocks request the data to be processed via a static callback function which reads one source row at a time (always the superset of "candidate" columns). the PL/SQL block in turn (in case it is called at all) processes the requested columns only. it handles errors so that the caller can request codes/messages in case the execution fails.

the described solution worked well. unfortunately it took almost double the time of the static PL/SQL solution.

question is: what do you think about this? do you see a better approach?

(i've read all your books ;o)

but our (internal) customers run delphi and share the same database schemas ...

they use (thin) delphi clients to access the database. these clients call pl/sql code residing on server side.

static code doesn't seem to be solution flexible enough for our requirements (and preference to do as much as possible on database side :), since there are no static rules how the data has to be transferred from source to target. in fact the migration routines may even vary for different transfers of the exact same input format.

i just mentioned delphi because i thought you thought that there would be pl/sql available at client side when you wrote "write a routine that dynamically generates the routines once at your customer site -- resulting in static, known sql/plsql code at each customer."

our current scenario consists of one "universal" static pl/sql routine at database side. it determines the "frame" for invokation of several anonymous pl/sql blocks (one per DML target) generated at runtime according to customers needs for processing user specific customized DML operations (e.g. exclude or transform certain column values during DML).

i'm afraid i didn't understand your solution at all.
could you please explain it in a little more detail?

but then it 's all about how/where to store those generated routines. even if we would assign one dedicated schema per customer to hold these customized database objects we would run into problems with multiple sessions (transferring different data) for one customer at a time.

Can you do this?

create or replace procedure wow
is
v_sql varchar2(1000);
begin

v_sql := 'create or replace procedure wowwow
is
begin
dbms_output.put_line(''Wowsers'');
end;
/
';
execute immediate (v_sql);
end;
/

After getting an ora-1031 insufficient privs on exec wow, I explicitly granted the 'create procedure' priv to my user, and re-executed.

Now I get:

eurokous@dev> exec wow
ERROR:
ORA-24344: success with compilation error
ORA-06512: at "eurokous.WOW", line 13
ORA-06512: at line 1

It's a problem with execute immediate. I'll read up, but what do you have to say?

Thanks much

Dear Tom,
When I do procedural SQL as

SQL>exec dbms_repcat_admin.do_sql('create table t(x int)');

 It affects the SYS schema.It is not stored in my catalog
but stored in SYS Schema.Why does this happen?
please do reply.

 

Hi Tom,
This procedure makes use of dynamic sql but it throws an error when called.

SQL>create or replace procedure var_p(x in number,y out varchar2) as
  2   c pls_integer default dbms_sql.open_cursor;
  3   n pls_integer;
  4   begin
  5    dbms_sql.parse(c,'Delete from empl where empno = :b1 returning ename into 

:b2',dbms_sql.native);
  6    dbms_sql.bind_variable(c,'b1',x);
  7    dbms_sql.bind_variable(c,'b2',y);
  8    n := dbms_sql.execute(c);
  9   dbms_sql.variable_value(c,':b2',y);
 10   dbms_sql.close_cursor(c);
 11  end;
SQL> /

Procedure created.

                                                                                

SQL> exec var_p(7902,:p)
BEGIN var_p(7902,:p); END;

*
ERROR at line 1:
ORA-03113: end-of-file on communication channel 


SQL> exec var_p(7934,:p)
BEGIN var_p(7934,:p); END;

*
ERROR at line 1:
ORA-03113: end-of-file on communication channel 


 

ops$tkyte@ORA9IR2> create or replace procedure var_p(x in number,y out varchar2)
  2  as
  3  begin
  4          execute immediate
  5      'delete from emp where empno = :x returning ename into :y' using IN x, OUT y;
  6  end;
  7  /
 
Procedure created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> variable y varchar2(200)
ops$tkyte@ORA9IR2> exec var_p( 7900, :y )
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> print y
 
Y
------------------------------------------------------------------------
JAMES
 

Dear Tom,
If I have a table like
  SQL> create table t(id number,description varchar2(30));
I would like to insert MANY rows into this table through *PL/SQL.*
How to do it?
For example rows like
 SQL> insert into t values(1,'One');
 SQL> insert into t values(2,'Two');
 SQL> insert into t values(3,'Three');

need to be inserted through PL/SQL.
Could you please help?

 

ops$tkyte@ORA9IR2> create table t ( id number, description varchar2(30) );
 
Table created.
 
ops$tkyte@ORA9IR2> begin
  2  insert into t
  3  select rownum, initcap(to_char(to_date(rownum,'j'),'jsp'))
  4    from all_objects
  5   where rownum <= 3;
  6  end;
  7  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select * from t;
 
        ID DESCRIPTION
---------- ------------------------------
         1 One
         2 Two
         3 Three
 

;)

but seriously -- not sure what you mean "through *PL/SQL.*"  

that seems to indicate to me that you are having a hard time getting started with plsql or something?  I mean - plsql is just a programming language, one that makes doing sql very easy.  do you have a specific issue? 

Dear Tom,
I meant,Can we make use of dbms_sql package to perform the
above mentioned operation USING bind arrays?I don't have any problem with pl/sql as you assume.

Hi Tom,

I don't know why it gave me 'ORA-01031 insufficient privileges' error even for this simple dynamic sql.  I can do it in anonymous bloc, but not in procedure. I tried in system user, but got the same error.  Here is the SQL.  

Thanks so much for your help.

----------------------------------------------------------
SQL> BEGIN
  2  execute immediate 'create table t(x int)';
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> drop table t;

Table dropped.

SQL> CREATE OR REPLACE PROCEDURE add_table IS
  2  BEGIN
  3  execute immediate 'create table t(x int)';
  4  END;
  5  /

Procedure created.

SQL> exec add_table
BEGIN add_table; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SCOTT.ADD_TABLE", line 3
ORA-06512: at line 1
-----------------------------------------------------
 

Hi Tom,

I learned so much from you, but I will always remember thie one.
-------------------------
SQL> connect system/password
Connected.
SQL> grant dba to scott;

Grant succeeded.

SQL> connect scott/tiger
Connected.
SQL> CREATE OR REPLACE PROCEDURE add_table IS
  2  BEGIN
  3     execute immediate 'create table t(x int)';
  4  END;
  5  /

Procedure created.

SQL> exec add_table
BEGIN add_table; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SCOTT.ADD_TABLE", line 3
ORA-06512: at line 1


SQL> connect system/password
Connected.
SQL> grant create procedure to scott;

Grant succeeded.

SQL> connect scott/tiger
Connected.
SQL> CREATE OR REPLACE PROCEDURE add_table IS
  2  BEGIN
  3     execute immediate 'create table t(x int)';
  4  END;
  5  /

Procedure created.

SQL> exec add_table
BEGIN add_table; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SCOTT.ADD_TABLE", line 3
ORA-06512: at line 1


SQL> set role none;

Role set.

SQL> create table t(x int);
create table t(x int)
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> connect system/password
Connected.

SQL> grant create table to scott;

Grant succeeded.

SQL> connect scott/tiger
Connected.
SQL> set role none;

Role set.

SQL> create table t(x int);

Table created.

SQL> drop table t;

Table dropped.

SQL> CREATE OR REPLACE PROCEDURE add_table IS
  2  BEGIN
  3     execute immediate 'create table t(x int)';
  4  END;
  5  /

Procedure created.

SQL> exec add_table

PL/SQL procedure successfully completed.
------------------------------------------ 

I have a package with ONE exposed procedure that accepts a parameter and executes a local (not in the package spec) procedure to do stuff.

Instead of validating the parameter and running the local procedure the traditional way, is there a easier way to do this in one go using the new associative arrays in 9iR2?

i.e. if I setup a assoc array like

proc_name('parameter1') := 'proc1';
proc_name('parameter2') := 'proc2';
...

Now, my main(), so to speak, has to simply do (in theory)

procedure process(p_parameter in varchar2)
is
begin
if proc_name.exists(p_parameter)
then
execute proc_name(p_parameter);
end if;
end;

Sort of like the 'eval' command in Unix shell scripts!

Of course, this doesnt work.

[I just thought that doing this versus a huge if/then/elsif statement is more elegant]

Any ideas? Thanks

I am not doing this lots so the overhead of dynamic SQL is acceptable for me.

Is there a way I can expose all the private procedures in the package spec but make them not really public? i.e. put some code to prevent direct execution of those procs? Only a designated "wrapper" proc (also public) is allowed to call these procs?

Thanks

we want to do some calculation within the database - based on different formulas that reference certain database entries, e.g.:

result := [placeholder1] + [placeholder2]

each formula is evaluated multiple times with changing input values for its placeholders (extracted from the database by using PL/SQL).

from our current point of view there are at least 3 options available:

1st: using dynamic SQL
prepare the formula by replacing their placeholders with appropriate SYS_Context-entries and opening a cursor (parse once, execute many)

1a: generate a PL/SQL-block, that calculates the result and invokes a procedure to store the result in a package variable accessible to the caller.

1b: generate a SQL-statement instead of a PL/SQL-block and receive the result via fetch

2nd: using JAVA
generate the result by invoking a (generated) parser, that evaluates the given expression

the 1st approach would introduce context-switches between PL/SQL and SQL, the 2nd one between PL/SQL and JAVA.

what option would you start prototyping with in order to compare/measure?

formulas are to be defined by the users at runtime.

calculated results and input values (for placeholders) are located within one table - but never within one row. formulas are stored outside that table but get assigned to certain entries of that table.

let me give you a simplified example:

id1 id2 value
1 'A' 30 <-- calculated: ['B'] + ['C'] for id1 = 1
1 'B' 10
1 'C' 20
2 'A' 70 <-- calculated: ['B'] + ['C'] for id1 = 2
2 'B' 35
2 'C' 35

here formula ['B'] + ['C'] is assigned to entries with id2 = 'A' and references entries with id2 in ('B', 'C'). by definition formulas always apply to entries with the same id1. id1 determines some kind of 'scope' for looking up the input values for the calculation.

what of our 3 approaches would you prefer (to check out first)?

i guess we have to check out ourselves what is more/less expensive:

... to do a lot of string processing to replace placeholders with values within the formulas in order to feed the parser (that 's doesn't perform any database call) with its input string ...

... or to use those context-entries to provide input to an anonymous PL/SQL-block or SQL-statement for calculation ...

which context-switch is more efficient: between PL/SQL and JAVA or PL/SQL and SQL? should one prefer the SQL-statement or is an anonymous PL/SQL-block the better choice?

sure, i agree in general, but we 've two reasons to prefer the database side:

1st: this "spreadsheet"-thing is just a small part in addition to an application, which is implemented at the database (and we don't want to spread it ;o)

2nd: we 've to minimize the need to transfer input data over the network just to calculate a result, that gets stored back into the same database.

so our questions remain after all: choose JAVA or dynamic SQL or "dynamic PL/SQL"?

Based on your requirements, I'd seriously consider 10g and the model.

</code> http://www.oreillynet.com/pub/a/network/2004/08/10/MODELclause.html <code>

Dear Sir,
I tried this procedure but it throws some errors.What is wrong with this?
Please do reply.

SQL>create or replace procedure psql3(jb in emp.job%type,salaries out dbms_sql.number_table)
  2  authid definer
  3  as
  4  begin
  5   execute immediate 'update emp set sal = sal * 1.2 where job = :x returning sal
  6                       bulk collect into :sals' using IN jb,OUT salaries;
  7* end;
SQL> /

Warning: Procedure created with compilation errors.

SQL> show err
Errors for PROCEDURE PSQL3:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/2      PL/SQL: Statement ignored
6/63     PLS-00457: expressions have to be of SQL types
 

Dear Tom,
Thanks for your reply.Could you please have a look at the code below?

SQL>  create or replace procedure p2(dno in number,enms out       vc2arr)
  2  authid current_user
  3  as
  4  begin
  5  execute immediate 'update e set sal = sal * 1.2 where deptno = :deptno returning
  6                     ename into :ename' using IN dno,OUT enms;
  7* end;
SQL> /

Procedure created.

SQL> desc vc2arr
 vc2arr TABLE OF VARCHAR2(30)

SQL> declare
  2  l_names vc2arr;
  3  begin
  4  p2(20,l_names); -- Procedure call
  5  for i in 1..l_names.count loop
  6   dbms_output.put_line(l_names(i));
  7  end loop;
  8  end;
  9  /
declare
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected SCOTT.VC2ARR got CHAR
ORA-06512: at "SCOTT.P2", line 5
ORA-06512: at line 4

How to correct this?
Please do reply.
Bye!
 

<b>don't use dynamic sql unless you have to and here you do not have to</b>

ops$tkyte@ORA9IR2> @test
ops$tkyte@ORA9IR2> create or replace type vc2arr as table of varchar2(30)
  2  /
 
Type created.
 
ops$tkyte@ORA9IR2> drop table emp;
 
Table dropped.
 
ops$tkyte@ORA9IR2> create table emp as select * from scott.emp;
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace procedure p2(dno in number,enms out            vc2arr)
  2  authid current_user
  3  as
  4  begin
  5  execute immediate '
  6  begin
  7    update emp set sal = sal * 1.2 where deptno = :deptno returning ename bulk collect into :ename;
  8  end;' using IN dno,OUT enms;
  9  end;
 10  /
 
Procedure created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2  l_names vc2arr;
  3  begin
  4  p2(20,l_names); -- Procedure call
  5  for i in 1..l_names.count loop
  6   dbms_output.put_line(l_names(i));
  7  end loop;
  8  end;
  9  /
SMITH
JONES
SCOTT
ADAMS
FORD
 
PL/SQL procedure successfully completed.
 

I dont get it, what is the difference between what you did

execute immediate '
begin
update emp set sal = sal * 1.2 where deptno = :deptno returning ename bulk collect into :ename;
end;' using IN dno,OUT enms;

and what the person was doing

execute immediate '
update e set sal = sal * 1.2 where deptno = :deptno returning ename into :ename' using IN dno,OUT enms;

He was simply missing the BULK COLLECT keyword in the RETURNING clause, right?

Why did you enclose your update in a anonymous PL/SQL block?

Thanks

Dear Tom,

I have some questions regarding dynamic sqls.

Goal is to achieve calling procedure or function dynamically with or without
parameters passed.

I want to call procedure/function depends on the method_id i receive.
If it is just procedure/functions without any parameters then i can
do it by dynamic sql. How to change the parameters dynamically depends
on the method?

-- Here Method means not java mothed. Just a procedure/function.

eg: In the below table all the methods with procedure name to be called are
stored. In the Process_Simple_Case procedure i receive the method_id so
i can fetch the procedure/function name from the table by method_id. My
question is these procedures/functions might have parameters.In this case
how i can call that procedure dynamically?

Create Table Define_Methods ( Country_Code Varchar2(10),
Method_Id Number(10),
Method_Name Varchar2(200) );

Insert Into Define_Methods ( Country_Code, Method_Id, Method_Name )
Values ( 'IND', 1, 'My_Package.Process1' );

Insert Into Define_Methods ( Country_Code, Method_Id, Method_Name )
Values ( 'IND', 2, 'My_Package.Process2' );

Insert Into Define_Methods ( Country_Code, Method_Id, Method_Name )
Values ( 'US', 1, 'My_Package.Process5' );

Insert Into Define_Methods ( Country_Code, Method_Id, Method_Name )
Values ( 'US', 5, 'My_Package.Process1' );

-- Methods

Create Or Replace Package My_Package Is

Procedure Process1( pStart_Date In Date,
pSeq_Id In Number );
Procedure Process2( pStart_Date In Date,
pEnd_Date In Date );
Procedure Process3( pSeq_Id In Number );
Procedure Process4;
Procedure Process5( pStart_Date In Date,
pEnd_Date In Date,
pSeq_Id In Number,
pDescription In Varchar2 );
End My_Package;


Create Or Replace Package Body My_Package Is

Procedure Process1( pStart_Date In Date,
pSeq_Id In Number )
Is
Begin
-- Some processing
Null;
End Process1;

Procedure Process2( pStart_Date In Date,
pEnd_Date In Date )
Is
Begin
-- Some processing
Null;
End Process2;

Procedure Process3( pSeq_Id In Number )
Is
Begin
-- Some processing
Null;
End Process3;

Procedure Process4
Is
Begin
-- Some processing
Null;
End Process4;

Procedure Process5( pStart_Date In Date,
pEnd_Date In Date,
pSeq_Id In Number,
pDescription In Varchar2 )
Is
Begin
-- Some processing
Null;
End Process5;

End My_Package;

Procedure Process_Simple_Case ( pMethod_Id In Number,
pStart_Date In Date,
pEnd_Date In Date,
pSeq_Id In Number,
pDescription In Varchar2 )
Is
Begin


Select Method_Name
Into lMethod_Name
Where Method_Id = pMethod_Id;

-- lMethod_Name can be function or procedure . But in this
-- example carries only procedure

Execute Immediate( 'Begin lMethod_Name(...parameters may change for each method) End;' Using pStart_Date, pEnd_Date....);

-- Bind variables after "Using" clause varies depends on the
-- no. of parameters passed.But the parameters will
-- be within the Process_Simple_Case parameters receiving.

End Process_Simple_Case;

Is it possible to achieve this? If not can you suggest any other way to do this?

Thanks and Regards,
Ibrahim.

Dear Tom,
Thanks for your inputs. I am using User_Arguments to make it 100% dynamic. I have a small question.

1) In your below example is it possible to send the parameters as a string after using clause.

eg:

lall_parameters varchar2(4000) := 'pStart_date, p_End_date, pSeq_id, pDescription';

l_block :=
'declare
l_start_date date := :x1;
l_end_date date := :x2;
l_seq_id number := :x3;
l_desc long := :x4;
begin
' || ...... passing l_ parameters || ';
end;';


execute immediate l_block using pStart_date, p_End_date, pSeq_id, pDescription;

Instead :

execute immediate l_block using lall_parameters;


Thanks and Regards,
Ibrahim.

Dear Tom,

Thanks for your reply. Below is my situation. Please help me with your valuable inputs. Thanks in advance.

Create Table Define_Methods ( Method_Id Number, Package_Name Varchar2(30), Object_Name Varchar2(30), Overload Varchar2(40) );

Insert Into Define_Methods Values ( 1, MY_PACKAGE, PROCESS1, Null );
Insert Into Define_Methods Values ( 2, MY_PACKAGE, PROCESS2, Null );
Insert Into Define_Methods Values ( 2, MY_PACKAGE, PROCESS5, Null );

-- Below PLSQL table is to hold the values for the using clause.
Type rParam_List Is Record ( pMode Varchar2(10),
pStart_Date Date,
pEnd_Date Date,
pSeq_Id Number,
pTotal_Amount Number,

pCode Varchar2(10),
pDescription Varchar2(200),
pAmount Number );
Type tParam_List Is Table Of rParam_List;

-- To get all the Parameters for the dynamic procedure/function.
Function Get_Method_Arguments ( pMethod_Id In Define_Methods.Method_Id%Type )
Return Method_Arguments Pipelined Is

lOverLoad Define_Methods.OverLoad%Type Default Null;
lPackage_Name Define_Methods.Package_Name%Type;
lObject_Name Define_Methods.Object_Name%Type;
lMethod_Arguments Method_Arguments_Rec := Method_Arguments_Rec( Null, Null, Null, Null ); -- SQL Type
Begin
Select Package_Name, Object_Name, OverLoad
Into lPackage_Name, lObject_Name, lOverLoad
From Define_Methods
Where Method_Id = pMethod_Id;

For rMethod_Args In ( Select Argument_Name, Position, Pls_Type, In_Out
From User_Arguments
Where Package_Name = lPackage_Name
And Object_Name = lObject_Name
And Nvl( lOverLoad, '-1' ) = Nvl( lOverLoad, '-1' )
Order By Position )
Loop
lMethod_Arguments.Argument_Name := rMethod_Args.Argument_Name;
lMethod_Arguments.Position := rMethod_Args.Position;
lMethod_Arguments.Pls_Type := rMethod_Args.Pls_Type;
lMethod_Arguments.In_Out := rMethod_Args.In_Out;
Pipe Row( lMethod_Arguments );
End Loop;
Return;
End Get_Method_Arguments;

-- Actual processing
Function Process_Simple_Case ( pMode In Varchar2,
pStart_Date In Date,
pEnd_Date In Date,
pSeq_Id In Number,
pTotal_Amount In Out Number )

Return Pls_Integer Is

lParam_List tParam_List;
lBlock Varchar2(1000);
lParameters Varchar2(2000);
lBind_Variables Varchar2(2000);
lPackage_Name Define_Methods.Package_Name%Type;
lObject_Name Define_Methods.Object_Name%Type;
Begin

For Rec In ( Select Code, Description, Amount, Method_Id
From Customer_Table )
Loop
lParam_List := tParam_List();

-- Construct the bind variables to be passed for the dynamic sql
lParam_List.Extend();

-- the below holds the parameters from the Process_Simple_Case function
lParam_List(1).pMode := pMode;
lParam_List(1).pStart_Date := pStart_Date;
lParam_List(1).pEnd_Date := pEnd_Date;
lParam_List(1).pSeq_Id := pSeq_Id;
lParam_List(1).pTotal_Amount := pTotal_Amount;

-- the below holds the Local Cursor variables/if any local variable defined.
-- this PLSQL is to achieve all the Using values to be kept so that it
-- doesnt from where it comes from or goes out. But this PLSQL table should
-- always have a single record in it.

lParam_List(1).pCode := Rec.Code;
lParam_List(1).pDescription := Rec.Description;
lParam_List(1).pAmount := Rec.Amount;


-- Construct the dynamice parameters and values after using clause
-- for the procedure/function to be called!
For rMethod_Arguments In ( Select Argument_Name, Position, Pls_Type, In_Out
From Table( Get_Method_Arguments ( Rec.Method_Id ) )
)
Loop
-- here i need to construct lParameters and lBind_Variables
lParameters := ???; -- this parameter we can get it
-- from the cursor and concatenate them


lBind_Variables := ???; -- how to do this?
-- Values should be bind parallel to the
-- lParameters.
-- All the parameters for the lParameters list
-- varies depends on the
-- cursor Argument list and also Bind Variables
-- which comes from the
-- PLSQL table also varry accordingly.
End Loop;

Select Package_Name, Object_Name
Into lPackage_Name, lObject_Name
From Prl_Ri_Available_Methods_Ref
Where Method_Id = Rec.Method_Id;

lBlock := 'Begin '||lPackage_Name||'.'||lObject_Name||'( '||lParameters||' ); End;';

Execute Immediate lBlock Using lBind_Variables;

End Loop;
Return;
End Process_Simple_Case;

Regards,
Ibrahim.

Dear Tom,
May be this simple example gives you clear picture.

Procedure Process_Simple_Case( pStart_Date In Date,
pEnd_Date In Date,
pSeq_Id In Number,
pCode In Varchar2 )
Is

lPackage_Name Varchar2(50) := 'My_Package';
lObject_Name Varchar2(50) := 'Process1';
lParamaeters Varchar2(50);
Begin
lParamaeters := ':pStart_Date, :pEnd_Date, :pSeq_Id';
lBlock := 'Begin '||lPackage_Name||'.'||lObject_Name||'( '||lParameters||'); End;';

--So the variable lBlock will look like this
lBlock := 'Begin My_Package.Process1( :pStart_Date, :pEnd_Date, :pSeq_Id ); End;';

Execute Immediate lBlock
Using lStart_Date, lEnd_Date, lCode;

This is fine but my problem is here it is Process1 it might be Process2/Process3 etc..
So the values after Using differs according to the Process. I understand fully we cannt dynamically
create the list for the Using clause values So how i can acheive this scenario? is it possible?

eg:

1) Execute Immediate 'Begin My_Package.Process1( :pStart_Date, :pEnd_Date, :pSeq_Id ); End;'
Using lStart_Date, lEnd_Date, lCode;

2) Execute Immediate 'Begin My_Package.Process2( :pSeq_Id, :pCode ); End;'
Using lSeq_Id, lCode;

and so on processes.

I know the parameter and the values but i need to pass the values only depends on the
what ever parameter goes in.

something like this:

lBlock := 'Begin '||lPackage_Name||'.'||lObject_Name||'( '||lParameters||'); End;';

Execute Immediate lBlock
Using ...

Depends on the parameters for the process which is contructed inside the lBlock the below values for Using clause should be passed.

After using clause is it possible to change the values dynamically?

End;

Thanks a lot Tom.

Regards,
Ibrahim.

Dear Sir,
This anonymous block hangs forever.I think the problem is due
to the execute_and_fetch procedure of dbms_sql package.How to
fix this?

SQL> declare
    2  v_deptno dept.deptno%type;
    3  c pls_integer default dbms_sql.open_cursor;
    4  begin
    5  dbms_sql.parse(c,'select deptno from dept',dbms_sql.native);
    6  dbms_sql.define_column(c,1,v_deptno);
    7  loop
    8  if dbms_sql.execute_and_fetch(c) = 0 then
    9  exit;
   10  end if;
   11  dbms_sql.column_value(c,1,v_deptno);
   12  dbms_output.put_line(v_deptno);
   13  end loop;
   14  dbms_sql.close_cursor(c);
   15  exception
   16  when others then
   17  if dbms_sql.is_open(c) then
   18   dbms_sql.close_cursor(c);
   19  end if;
   20*  end;
  
How to use the execute_and_fetch procedure?Could you please help?
Please do reply.
Bye!
 

Hi Tom,
Using dbms_sql package,Is it possible to fetch an entire row
rather tahn fetching individual column values?I tried this procedure but I find it difficult to complete it.Could you
please tell me how to proceed?The procedure is as follows.

SQL> create or replace procedure proc(enm in emp.ename%type default 'BLAKE')
as
c pls_integer default dbms_sql.open_cursor;
begin
dbms_sql.parse(c,'select * from emp where ename = :ename',dbms_sql.native);
dbms_sql.bind_variable(c,':ename',enm);

How to fetch an entire row in dbms_sql?Can here the procedure
execute_and_fetch be used?

Please do reply.

 

Is there anything that I am missing :

I have a stored proc wherein I insert into a global temp table as select * from my_tbl. This operation takes 1 sec ( for 30000 rows) according to tkrprof output. I thought of creating a table and an index with nologging dynamically.

create or replace procedure gt1
as
begin
execute immediate 'create table gt1 nologging as select * from my_tbl ';
end;
/

When executing this procedure, I get insufficient privileges. Same users' table being accessed.

Actually I meant to use global temp table in the execute immediate script. When running from sqlplus and it runs fine in no time compared to the insert of 1 sec that creates 430708 redo size.
begin
execute immediate 'create global temporary table gt6 on commit preserve rows as select * from t5 ';
end;
/
Elapsed: 00:00:00.00

So in your opinion, this is ok of creating global temp tbl through the stored proc. The whole idea here is to quicken the data insert into a temp table for further processing.

no, it is my learned experience that this would be a horrible idea.

doing ddl in a stored procedure is not a good idea at all.


lets see the tkprof showing the insert taking 1 second vs CTAS taking 0.00

not that I don't believe you -- but... 0.00 is sort of SHORT for a CTAS without any data.



ops$tkyte@ORA9IR2> drop table gt6;
 
Table dropped.
 
Elapsed: 00:00:00.03
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> begin
  2    execute immediate 'create global temporary table gt6 on commit preserve rows as
  3    select * from big_table.big_table where rownum < 40000 ';
  4  end;
  5  /
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.38
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> truncate table gt6;
 
Table truncated.
 
Elapsed: 00:00:00.02
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into gt6 select * from big_table.big_table where rownum <40000;
 
39999 rows created.
 
Elapsed: 00:00:00.18



and when I run tkprof I'm not seeing any "1 second vs 0 seconds" 

In the following procedure my_tbl belongs to THIRU and the procedure global_temp_sp is created in THIRU. So I did not understand why insufficient privs error.

SQL> create or replace procedure global_temp_sp
  2  as
  3  begin
  4  execute  immediate 'create global temporary table gt3  as select * from my_tbl';
  5  end;
  6  /

Procedure created.

Elapsed: 00:00:00.00
SQL> begin
  2  global_temp_sp;
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "THIRU.GLOBAL_TEMP_SP", line 4
ORA-06512: at line 2

Whereas if I do like this, there is no error:

SQL> begin
  2  execute  immediate 'create global temporary table gt3  as select * my_tbl ';
  3  end;
  4  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

Thanks for the time. 

Hi Tom,
At the end of the procedure run, I want to be able to refresh the table on another server.
Here is what works in SQL*Plus:
Execute dbms_snapshot.refresh ('"UB_OBJECTS"."BO_SORD_SUMMARY"', 'c');
Here is what I am trying to do dinamically:
SELECT ''''||'dbms_snapshot.refresh'||'('||''''||'"'||'UB_OBJECTS'||'"'||'.'||'"'||'BO_SORD_SUMMARY'||'"'||''''||','||''''||'c'||''''||')'||''''
INTO vRep_string
FROM dual;
EXECUTE IMMEDIATE vRep_string;
I am getting "INVALID SQL STATEMENT" error
Why?
Could you, please help.

Thanks,
Eugene

Begin
Execute immediate t_exp into t_remark ;
dbms_output.put_line(t_remark);
Exception
when no_data_found then
....
when others then
.....
end;

In the above, how to catch the exception if the sql (t_exp) is not a valid sql ie. having semantic or Syntax error. (ofcourse others is catching, but any unique exception available?)

ops$tkyte@ORA9IR2> declare
  2      invalid_sql exception;
  3      pragma exception_init( invalid_sql, -900 );
  4
  5      l_str  long;
  6      l_data long;
  7  begin
  8      l_str := 'foobar';
  9      execute immediate l_str into l_data;
 10  exception
 11      when INVALID_SQL
 12      then
 13          dbms_output.put_line( '"' || l_str || '" is invalid sql');
 14          raise;
 15  end;
 16  /
"foobar" is invalid sql
declare
*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at line 14
 

I have a tiny private procedure in a larger package that validates its input according to some rules

procedure foo (p_arg in varchar2,p_value in out varchar2)
is
begin
-- validate stuff
if (error) then
raise_application_error(-20000,'Arg: '||p_arg||' Invalid value: '||p_value);
end if;
p_value := p_arg;
end;

Here, for p_arg, I really want the name of the argument passed in, not its value

Yes, I can do

foo('l_arg',l_arg,l_value);

But the code looks not so good.

Is there a way to pass in a 'string' and "dereference" it?

Sort of like pointer variables in C and dereferencing them with the *variable notation?

Thanks

"I don't understand why you have to pass 'l_arg' and l_arg -- doesn't l-arg have the name in it? "

l_arg is the value of the variable l_arg.

In my error message, I want to say that the parameter passed in ('l_arg' had a bad value l_arg (without the quotes)

I guess

foo('l_arg',l_arg,...) is the only way?

Thanks

l_value is the value I want to set it to. Its a component of the %rowtype variable for a record in the target table, its passed in as a OUT variable to foo

If l_arg passes validation, I will assign l_value := l_arg

Oh well, its just that foo('l_arg',l_arg,l_value) looks a little odd.

My C pointer analogy was not correct, I meant the 'eval' type of feature in Unix shells where you can do this kind of stuff like

x=5
y=x
eval echo '$'$y

this prints 5

Hi

I need to convert a PRO*C code into PL/SQL.

I have two tables, t1 and t2

t1 has three columns, cod_area, nam_area, description

t2 has four columns cod_area, mod_date, nam_area, description

t2 is basically a table to support incremental modifications, mod_date is the modification date

so whenever there is a row in t2 and that row is in t1 what we do is update t1 with values in t2, however we only update the column where the values are different, let´s say only nam_area is modified which was YORKSHIRE after modification it becomes LONDON, so instead of

update t1
set (cod_area, nam_area, description) = (select cod_area, nam_area, description from t2 where cod_area = 1)
where cod_area = 1;

we do

update t1
set nam_area = 'LONDON'
where cod_area = 1

the PRO*C does this, it first queries t1 and t2

select * from t1 where cod_area = 1
select cod_area, nam_area, description from t2 where cod_area = 1

store the output in variables then uses strcmp to compare variables from both result sets, if different it builds a string for dynamic SQL, can we achieve this with PL/SQL?

I am arguing with the support guy, I am telling him that doing this sort of operation is easier with PL/SQL but he insists PRO*C is much faster.

Hi

I am told not to update all row because there are bitmap indexes and bitmap indexes needs to be rebuilt very often when there are DML occuring in the indexed columns.

I suggested the same too, updating the row and forget it but the guy uses the bitmap index theory

:)  -- couple of things.

first, they seem to be updating "slow by slow" (eg: row by row).  If your primary goal is to destroy a bitmap index, that is by far THE SINGLE BEST way to do it!!!

(that is, their approach is 100% counter to their argument)


if you want a bitmap to be well preserved -- you try to do as much as you humanly can in a single SQL statement.

Second, when you update a column to it's current value (eg: you do not change the value), we skip the index alltogether.  Optimization for tools that update every column in a table even if they are not changed (eg: forms always used to do that...)  

Consider:


ops$tkyte@ORA9IR2> create table t       as select * from big_table.big_table where rownum <= 100000;
 
Table created.
 
ops$tkyte@ORA9IR2> create table t2 as select * from t;
 
Table created.
 
ops$tkyte@ORA9IR2> alter table t2 add constraint t2_pk primary key(id);
 
Table altered.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table istats as select rpad('x',10,'x') when, index_stats.* from index_stats where 1=0;
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> begin
  2      for x in (select column_name from user_tab_columns where table_name = 'T' )
  3      loop
  4          execute immediate 'create bitmap index bm_' || x.column_name || ' on t(' || x.column_name || ')';
  5          execute immediate 'analyze index bm_' || x.column_name || ' validate structure';
  6          insert into istats select '1 before', index_stats.* from index_stats;
  7          dbms_stats.gather_index_stats( user, 'BM_' || x.column_name );
  8      end loop;
  9  end;
 10  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> @p
ops$tkyte@ORA9IR2> create or replace procedure prt ( p_str in varchar2 )
  2  is
  3     l_str   long := p_str;
  4  begin
  5     loop
  6        exit when l_str is null;
  7        dbms_output.put_line( substr( l_str, 1, 250 ) );
  8        l_str := substr( l_str, 251 );
  9     end loop;
 10  end;
 11  /
 
Procedure created.
 
ops$tkyte@ORA9IR2> declare
  2      l_stmt long;
  3  begin
  4      l_stmt := 'update ( select ';
  5      for x in (select column_name from user_tab_columns where table_name = 'T' )
  6      loop
  7          l_stmt := l_stmt || ' t1.' || x.column_name || ' t1_' || x.column_name || ',';
  8          l_stmt := l_stmt || ' t2.' || x.column_name || ' t2_' || x.column_name || ',';
  9      end loop;
 10      l_stmt := rtrim( l_stmt, ',' ) || ' from t t1, t2 where t1.id = t2.id ) set ';
 11      for x in (select column_name from user_tab_columns where table_name = 'T' and column_name <> 'ID')
 12      loop
 13          l_stmt := l_stmt || ' t1_' || x.column_name || ' = ' || ' t2_' || x.column_name || ',';
 14      end loop;
 15      l_stmt := rtrim( l_stmt, ',' );
 16      execute immediate l_stmt;
 17      dbms_output.put_line( sql%rowcount || ' rows modified' );
 18      prt( l_stmt );
 19      for x in (select column_name from user_tab_columns where table_name = 'T' )
 20      loop
 21          execute immediate 'analyze index bm_' || x.column_name || ' validate structure';
 22          insert into istats select '2 after', index_stats.* from index_stats;
 23      end loop;
 24  end;
 25  /
<b>100000 rows modified</b>
update ( select  t1.ID t1_ID, t2.ID t2_ID, t1.OWNER t1_OWNER, t2.OWNER t2_OWNER, t1.OBJECT_NAME t1_OBJECT_NAME, t2.OBJECT_NAME t2_OBJECT_NAME, t1.SUBOBJECT_NAME t1_SUBOBJECT_NAME, t2.SUBOBJECT_NAME t2_SUBOBJECT_NAME, t1.OBJECT_ID t1_OBJECT_ID, t2.OBJ
ECT_ID t2_OBJECT_ID, t1.DATA_OBJECT_ID t1_DATA_OBJECT_ID, t2.DATA_OBJECT_ID t2_DATA_OBJECT_ID, t1.OBJECT_TYPE t1_OBJECT_TYPE, t2.OBJECT_TYPE t2_OBJECT_TYPE, t1.CREATED t1_CREATED, t2.CREATED t2_CREATED, t1.LAST_DDL_TIME t1_LAST_DDL_TIME, t2.LAST_DDL_
TIME t2_LAST_DDL_TIME, t1.TIMESTAMP t1_TIMESTAMP, t2.TIMESTAMP t2_TIMESTAMP, t1.STATUS t1_STATUS, t2.STATUS t2_STATUS, t1.TEMPORARY t1_TEMPORARY, t2.TEMPORARY t2_TEMPORARY, t1.GENERATED t1_GENERATED, t2.GENERATED t2_GENERATED, t1.SECONDARY t1_SECONDA
RY, t2.SECONDARY t2_SECONDARY from t t1, t2 where t1.id = t2.id ) set  t1_OWNER =  t2_OWNER, t1_OBJECT_NAME =  t2_OBJECT_NAME, t1_SUBOBJECT_NAME =  t2_SUBOBJECT_NAME, t1_OBJECT_ID =  t2_OBJECT_ID, t1_DATA_OBJECT_ID =  t2_DATA_OBJECT_ID, t1_OBJECT_TYP
E =  t2_OBJECT_TYPE, t1_CREATED =  t2_CREATED, t1_LAST_DDL_TIME =  t2_LAST_DDL_TIME, t1_TIMESTAMP =  t2_TIMESTAMP, t1_STATUS =  t2_STATUS, t1_TEMPORARY =  t2_TEMPORARY, t1_GENERATED =  t2_GENERATED, t1_SECONDARY =  t2_SECONDARY
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> column leaf format a15
ops$tkyte@ORA9IR2> column branch format a10
ops$tkyte@ORA9IR2> column hgt format 99
ops$tkyte@ORA9IR2> column blk format 999
ops$tkyte@ORA9IR2> column dlr format 99
ops$tkyte@ORA9IR2> break on name skip 1
ops$tkyte@ORA9IR2> select name, when, height hgt, blocks blk, lf_rows||'/'||lf_blks||'/'||lf_blk_len leaf, br_rows ||'/'||br_blks||'/'||br_blk_len branch, del_lf_rows dlr
  2    from istats
  3   order by name, when;
 
NAME              WHEN       HGT  BLK LEAF            BRANCH     DLR
----------------- ---------- --- ---- --------------- ---------- ---
BM_CREATED        1 before     2   32 753/25/7996     24/1/8028    0
                  2 after      2   32 753/25/7996     24/1/8028    0
 
BM_DATA_OBJECT_ID 1 before     2   16 1362/9/7996     8/1/8028     0
                  2 after      2   16 1362/9/7996     8/1/8028     0
 
BM_GENERATED      1 before     2    8 6/3/7996        2/1/8028     0
                  2 after      2    8 6/3/7996        2/1/8028     0
 
BM_ID             1 before     2  384 100000/348/7996 347/1/8028   0
                  2 after      2  384 100000/348/7996 347/1/8028   0
 
BM_LAST_DDL_TIME  1 before     2   32 828/26/7996     25/1/8028    0
                  2 after      2   32 828/26/7996     25/1/8028    0
 
BM_OBJECT_ID      1 before     2  256 27935/128/7996  127/1/8028   0
                  2 after      2  256 27935/128/7996  127/1/8028   0
 
BM_OBJECT_NAME    1 before     2  256 16099/133/7996  132/1/8028   0
                  2 after      2  256 16099/133/7996  132/1/8028   0
 
BM_OBJECT_TYPE    1 before     2   16 29/5/7996       4/1/8028     0
                  2 after      2   16 29/5/7996       4/1/8028     0
 
BM_OWNER          1 before     2    8 25/3/7996       2/1/8028     0
                  2 after      2    8 25/3/7996       2/1/8028     0
 
BM_SECONDARY      1 before     2    8 6/3/7996        2/1/8028     0
                  2 after      2    8 6/3/7996        2/1/8028     0
 
BM_STATUS         1 before     2    8 7/3/7996        2/1/8028     0
                  2 after      2    8 7/3/7996        2/1/8028     0
 
BM_SUBOBJECT_NAME 1 before     2    8 35/3/7996       2/1/8028     0
                  2 after      2    8 35/3/7996       2/1/8028     0
 
BM_TEMPORARY      1 before     2    8 7/3/7996        2/1/8028     0
                  2 after      2    8 7/3/7996        2/1/8028     0
 
BM_TIMESTAMP      1 before     2   32 791/26/7996     25/1/8028    0
                  2 after      2   32 791/26/7996     25/1/8028    0
 
 
28 rows selected.

<b>so, we updated every column for 100,000 rows -- and each column was bitmap indexed and.....

it didn't touch them.

And if it had -- it would have done THE LEAST AMOUNT OF DAMAGE TO THEM since bitmaps LOVE bulk operations</b>


So, if their goal is

a) speed
b) least amount of damage to the bitmap

they have done the single worst thing they could -- slow by slow processing!


 

Hi

The process goes row by row, it´s compulsory because it´s an ETL Process.

My question is, are you saying that when we update the whole row if the value of the bitmap indexed columns are not changed the bitmap index size remains the same?

I will try a test now.

Cheers

Hi

The process is done row by row because each row are treated seperately with some logic (if else and some calculations). Also the exceptions needs to be handled for each row, we can have different situations that´s why a bulk update wouldnt work.

I will carry the test tonight by updating row by row some bitmapped columns


Cheers

Hi

I have been looking the code, my thoughts were as yours, this can be done in bulk but after reviewing the requirements and code analysis I conclude that this has to be done row by row. This is one of those typical processes where if the row is there then update otherwise insert just that instead of updating the whole row only modified portions need to be modified.

I am not what´s the correct way to do this? We are using 8.1.7.4 so merge statement cant be used

Cheers

So abstacting from that static SQL should be used whenever possible and dynamic if only truly needed I'm a bit confused.
So i took your approach and created two procedures:
create or replace procedure inst1 is
begin
for i in 1..10000 loop
execute immediate 'insert into t values (1, 1)';
end loop;
end;
/
create or replace procedure inst2 is
a number :=1;
b number := 1;
begin
for i in 1..10000 loop
execute immediate 'insert into t values (:a, :b)' using a, b;
end loop;
end;
/
Running them with runstats showed that first one runs a little faster, has little less latches (~10%). Can you shed some light on it?

Hi

I ran the test, when the bitmap indexed columns are not updated then the index size remains the same.

This leads to me to do another test testing out why update only modified column is better, it turns out modifying only the modified columns is faster which is logical I think. I guess we must do it this way since out tables has around 150 columns!



create table mega_tabla nologging
as
select rownum id, a.* from dba_objects a where 1 = 0;

insert /*+ append */ into mega_tabla
select '' id, a.* from dba_objects a;

commit;

insert /*+ append */ into mega_tabla
select a.* from mega_tabla a
union all
select a.* from mega_tabla a
union all
select a.* from mega_tabla a
union all
select a.* from mega_tabla a
union all
select a.* from mega_tabla a
union all
select a.* from mega_tabla a;

commit;

insert /*+ append */ into mega_tabla
select a.* from mega_tabla a
union all
select a.* from mega_tabla a
union all
select a.* from mega_tabla a
union all
select a.* from mega_tabla a
union all
select a.* from mega_tabla a
union all
select a.* from mega_tabla a;

commit;

update mega_tabla set id = rownum ;

commit;

create table t
as select * from mega_tabla
where rownum <= 10000;

create table t2 as select * from t;

alter table t add constraint t_pk primary key(id);

alter table t2 add constraint t2_pk primary key(id);

begin
for i in (select * from t where id between 1 and 10000)
loop
update t2 set
OBJECT_NAME = i.OBJECT_NAME
where t2.id = i.id+1;
end loop;
end;
/

exec runStats_pkg.rs_start

begin
for i in (select * from t2 where id between 1 and 10000)
loop
update t set t.object_name = i.object_name where t.id = i.id;
end loop;
end;
/

exec runStats_pkg.rs_middle

begin
for i in (select * from t2 where id between 1 and 10000)
loop
update t set OWNER = i.OWNER,
OBJECT_NAME = i.OBJECT_NAME,
SUBOBJECT_NAME = i.SUBOBJECT_NAME,
OBJECT_ID = i.OBJECT_ID,
DATA_OBJECT_ID = i.DATA_OBJECT_ID,
OBJECT_TYPE = i.OBJECT_TYPE,
CREATED = i.CREATED,
LAST_DDL_TIME = i.LAST_DDL_TIME,
TIMESTAMP = i.TIMESTAMP,
STATUS = STATUS,
TEMPORARY = i.TEMPORARY,
GENERATED = i.GENERATED,
SECONDARY = i.SECONDARY
where t.id = i.id;
end loop;
end;
/

exec runStats_pkg.rs_stop

exec runStats_pkg.rs_stop
Run1 ran in 1645 hsecs
Run2 ran in 1854 hsecs
run 1 ran in 88,73% of the time

Name Run1 Run2 Diff
LATCH.dml lock allocation 1 0 -1
STAT...SQL*Net roundtrips to/f 4 5 1
STAT...execute count 10,006 10,007 1
STAT...parse count (total) 7 8 1
STAT...redo log space requests 0 1 1
STAT...write clones created in 0 1 1
STAT...session cursor cache co 1 2 1
STAT...redo entries 10,005 10,006 1
STAT...redo buffer allocation 0 1 1
STAT...opened cursors cumulati 7 8 1
LATCH.archive control 0 2 2
STAT...user calls 6 8 2
STAT...session cursor cache hi 5 3 -2
LATCH.loader state object free 0 2 2
STAT...parse count (hard) 0 2 2
STAT...enqueue requests 5 3 -2
LATCH.archive process latch 0 2 2
LATCH.session idle bit 12 16 4
STAT...redo log space wait tim 0 4 4
STAT...consistent changes 21 17 -4
LATCH.active checkpoint queue 8 14 6
LATCH.list of block allocation 6 0 -6
LATCH.Token Manager 0 7 7
STAT...change write time 92 100 8
STAT...calls to get snapshot s 10,011 10,020 9
STAT...consistent gets 40,013 40,023 10
LATCH.row cache objects 0 12 12
STAT...messages sent 14 26 12
LATCH.enqueue hash chains 5 20 15
LATCH.redo allocation 10,039 10,067 28
LATCH.enqueues 9 40 31
LATCH.redo writing 52 88 36
LATCH.messages 80 135 55
STAT...recursive cpu usage 380 441 61
STAT...CPU used by this sessio 409 482 73
STAT...CPU used when call star 409 482 73
LATCH.cache buffers lru chain 375 450 75
LATCH.library cache 20,053 20,134 81
LATCH.shared pool 1 102 101
STAT...bytes sent via SQL*Net 540 653 113
LATCH.checkpoint queue latch 651 766 115
STAT...db block changes 20,156 20,302 146
STAT...free buffer requested 133 283 150
STAT...db block gets 10,282 10,577 295
STAT...session logical reads 50,295 50,600 305
LATCH.cache buffers chains 111,220 111,866 646
STAT...bytes received via SQL* 930 1,648 718
STAT...session uga memory max 5,912 4,236 -1,676
STAT...session uga memory 15,344 4,236 -11,108
STAT...redo size 2,671,132 5,084,448 2,413,316

Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
142,513 143,724 1,211 99.16%



for 10000 rows it generates 50% more redo, since we must process 10 million per day I think we should modify only the modified rows what do you think?

Tom concluded: "I think code should be easy, small, maintainable and done IN BULK."

To Xavi:

Fast and efficient are terrific goals.

MAYBE if Tom were the lead programmer in this project, could see the source, know the entire business environment and requirements, he MIGHT (or might not) agree that modifying only the columns that changed made sense. But I suspect he'd STILL work out some way to do this using bulk operations.

You "modify as needed" code might work now, but pity the next coder to maintain it. If they break some little feature you've worked in because they couldn't understand what was happening, you're hosed.

Besides, this site, while for the real world, is so all of us can learn from Tom's expertise. In the real world, in all but the most unique cases, his mantra works best:

"I think code should be easy, small, maintainable and done IN BULK."

Tom
In order to create a generic procedure for our specific need I was trying to to write a code like this.

declare

TYPE cur_type IS ref CURSOR ;
TYPE rec_type IS record ( col user_tab_columns.column_name%TYPE );
mycur cur_type ;
myrec rec_type ;

rec1 sgst1%rowtype ;
rec2 sgst2%rowtype ;

s varchar2(100);
sql_stmt VARCHAR2(200);

begin
select * into rec1 from sgst1 WHERE ROWNUM < 2 ;
select * into rec2 from sgst2 WHERE ROWNUM < 2 ;
--- comapre logic
open mycur FOR 'select column_name from all_tab_columns where table_name = sgst1 ' ;
LOOP
FETCH mycur into myrec ;
exit WHEN mycur%notfound ;
sql_stmt := ' select rec1.'||myrec.col ||' FROM dual ' ;
EXECUTE IMMEDIATE sql_stmt into s ;
END LOOP ;
END ;
/

when i run this

I get following

ERROR at line 1:
ORA-00904: "SGST1": invalid identifier
ORA-06512: at line 14

Please explain why this is not working ...

thanks
Sanjeev

Ok

Let me clarify
Generic.. I wanted to write a code for comparing records.

Rownum here was here to create two records in this sample code.

My attempt is to fetch value for a perticular column from the record. and I am trying to do this with the help of dynamic SQL. In order to get the column name i an using all_tab_columns.

Sanjeev

Yes and about the repeatedly putting value into same variable..I have not added code that will actual compare two records because of the error..

Sanjeev

don't. bad idea, horrible idea. besides, you are not going to dynamically
access a "record" like that.


=> Yes ,, in fact that is the question.. why cant we do that ?

if you want to compare two rows - I can show you the easy fast sql for that.

=> are u talking of minus ?

to compare two records -- well, you the programmer already know how to do that, no generic code for that. (you the programmer *better* know the fields don't you
think)

=> u mean that is not possible.. The need was to send a mail if thee is a difference. Mail only those columns those have differences.

Sanjeev

Hi!
I am trying to put value in varaiable according to the cursor value.eg

SQL> select loc,code
2 from site
3 /

L CODE
- ---------
A 300
B 100

declare
adept NUMBER;
bdept NUMBER;
begin
for i_Rec in (select loc,code from site) loop
irec.loc||'dept' := irec.code;
end loop;
end;

but error come. is it possible by using execute immediate or by any way 

thanx
 

hi!

>>sorry, but I cannot decipher...

SQL> desc site
 Name                            Null?    Type
 ------------------------------- -------- ----
 LOC                                      CHAR(1)
 CODE                                     NUMBER


SQL> select loc,code
  2  from   site
  3  /

L      CODE
- ---------
A       300
B       100

declare
adept NUMBER;
bdept NUMBER;
begin
for iRec in (select loc,code from site) loop
if irec.loc = 'A' THEN
adept:= irec.code;
elsif irec.loc = 'B' THEN
bdept:=irec.code;
end if;
end loop;
end;

for loc "A" i have variable adept and for loc "B" i have bdept variable.
in above code i have use if then else. i want to avoid this
because i have 50 to 60 statements, thats why i want to do like this

irec.loc||'dept' := irec.code;
mean if irec.loc will "A" it's valaue will be return in adept variable and in case of "B" bdept will receive the value.

can this be done using dynamic sql... 

thanx  

why would you have 50 variables named that?


I would

ops$tkyte@ORA9IR2> declare
  2          type array is table of number index by varchar2(30);
  3
  4          l_data  array;
  5  begin
  6          for x in ( select dname loc, deptno code from scott.dept )
  7          loop
  8                  l_data(x.loc) := x.code;
  9          end loop;
 10
 11
 12          dbms_output.put_line( 'Accounting ' || l_data( 'ACCOUNTING' ) );
 13          dbms_output.put_line( 'Research   ' || l_data( 'RESEARCH' ) );
 14  end;
 15  /
Accounting 10
Research   20
 
PL/SQL procedure successfully completed.



if you need this, 50 variables -- you would be "if elsifing" all over the place 

Dear Tom,

In you answer earlier in this topic, you suggested to user following structure:
l_block :=
'declare
l_start_date date := :x1;
l_end_date date := :x2;
l_seq_id number := :x3;
l_desc long := :x4;
begin
' || ...... passing l_ parameters || ';
end;';
for dynamic calls of the stored procedure. I know that it is very important to use bind variables for dynamic SQL, but would it make much difference if I put parameter values straight into dynamic pl/sql procedure call?
E.G
......
lsSql := 'begin my_procedure1(';
for i in ( select value,
datatype
from Proc_parameter pp,
proc_to_run p
where p.proc_name = 'MY_PROCEDURE1'
and p.status = 'NEW'
and p.id = pp.run_id
order by pp.param_orderno )
loop
if (i.datatype = TYPE_BOOLEAN) then
if (upper(i.value) = 'Y' ) then
lsParam := 'TRUE';
else
lsParam := 'FALSE';
end if;
elsif (i.datatype = TYPE_NUMBER) then
lsParam := i.value;
elsif (i.datatype = TYPE_STRING) then
lsParam := ''''||i.value||'''';
end if;

lsSql := lsSql || lsParam || ',';
end loop;
execute immediate lsSQL;
......

Thank you so much for your help.

Dear Tom,

Thank you for your reply. I understand that binds are better, but I am confused on how to do it in situation. I have table PROC which contains list of procedures to be called and table ATTR which contains parameters definitions used in calling of the stored procedures from PROC. These tables linked many to many via PROC_ATTR table.
I also have table PROC_TO_RUN which contains procedures to be executed and PROC_PARAMETER which contains links to ATTR, PROC_TO_RUN and actual value of the parameter.
Unfortunately I don't see how I can use suggested by you approach because I have over 100 unique parameters in table ATTR, but each procedure has normally less than 10 of them.
Please suggest the best way of using bind variables instead of "hard coded" values in the dynamic call to those procedures.
Thank you very much for your help.

create or replace function wipckfun(m_item varchar2,m_yyyymm number) return number is
--m_item varchar2(10):='330120120';
--m_yyyymm number(6):=200501;
m_opqty number(12,3);
m_recqty1 number(12,3);
m_recqty2 number(12,3);
m_recqty3 number(12,3);
m_pcqty number(12,3);
m_rejqty number(12,3);
m_srsqty number(12,3);
m_srsqty2 number(12,3);
m_pmrqty number(12,3);
m_pmiqty number(12,3);
m_adjqty number(12,3);
totqty number(12,3);
pqty number(12,3);
nqty number(12,3);
begin
select sum(open_qty) into m_opqty from wipledcb
where yyyymm = m_yyyymm
and itemcode=m_item;
dbms_output.put_line('opqty '||m_opqty);
------------------------
select sum(quantity) into m_recqty1
from dirmat.dayledger
where transtag = '2'
and cardcode in ('MRCIV')
and to_number(to_char(confirm_date,'yyyymm')) >= m_yyyymm
and itemcode=m_item;
dbms_output.put_line('mrciv '||m_recqty1);
------------------------
select sum(batchqty) into m_recqty2
from workorder.workorder_header
where to_number(to_char(authorisedon,'yyyymm')) >= m_yyyymm
and nvl(status,0) = 'Y'
and rmitem=m_item;
dbms_output.put_line('workorder '||m_recqty2);
------------------------
select sum(issued_qty) into m_recqty3
from mtl_request
where nvl(issued_qty,0) !=0
and to_char(updated_on,'yyyymm') >= m_yyyymm
and rmitem=m_item;
dbms_output.put_line('mtl_req '||m_recqty3);
------------------------
select sum(PCQTY) into m_pcqty
from prodstrpcblow
where yyyymm >=m_yyyymm
and minor=m_item;
dbms_output.put_line('pcqty '||m_pcqty);
------------------------
select sum(rejqty) into m_rejqty
from prodstrrejblow
where rejtype!='MATERIAL'
and yyyymm >=m_yyyymm
and minor=m_item;
dbms_output.put_line('rejqty '||m_rejqty);
------------------------
select sum(srsqty) into m_srsqty
from prodstrsrsblow
where yyyymm >=m_yyyymm
and minor=m_item;
dbms_output.put_line('srsqty '||m_srsqty);
------------------------
select sum(rejqty) into m_srsqty2
from prodstrrejblow
where rejtype='MATERIAL'
and yyyymm >=m_yyyymm
and minor=m_item;
dbms_output.put_line('srsqty2 '||m_srsqty2);
------------------------
select sum(pmrqty) into m_pmrqty
from prodstrpmrblow
where yyyymm >=m_yyyymm
and minor=m_item;
dbms_output.put_line('pmrqty '||m_pmrqty);
------------------------
select sum(pmiqty) into m_pmiqty
from prodstrpmiblow
where yyyymm >=m_yyyymm
and minor=m_item;
dbms_output.put_line('pmiqty '||m_pmiqty);
------------------------
select sum(qty) into m_adjqty
from wipadj
where to_number(to_char(docdate,'yyyymm')) >=m_yyyymm
and status != '9'
and item=m_item;
dbms_output.put_line('adjqty '||m_adjqty);
------------------------
pqty:=nvl(m_opqty,0)+nvl(m_recqty1,0)+nvl(m_recqty2,0)+
nvl(m_recqty3,0)+nvl(m_pmiqty,0)+nvl(m_adjqty,0);
nqty:=nvl(m_pcqty,0)+nvl(m_rejqty,0)+nvl(m_srsqty,0)+nvl(m_srsqty2,0)+nvl(m_pmrqty,0);
dbms_output.put_line('pqty '||pqty||' nqty '||nqty);
totqty:=(nvl(m_opqty,0)+nvl(m_recqty1,0)+nvl(m_recqty2,0)+
nvl(m_recqty3,0)+nvl(m_pmiqty,0)+nvl(m_adjqty,0))-
(nvl(m_pcqty,0)+nvl(m_rejqty,0)+nvl(m_srsqty,0)+nvl(m_srsqty2,0)+nvl(m_pmrqty,0));
dbms_output.put_line('total '||totqty);
return(totqty);
end;

Above procedure is working fine but
After seeing the above reviews I feel above procedure is very bad way of coding
Hello Tom can you give some tips to convert the above procedure using dynamic method or some good method

CREATE OR REPLACE PROCEDURE sp_test IS

r NUMBER;
t varchar2(30) := 'd_customer';
x varchar2(200);

BEGIN

execute immediate 'select count(*) from :x' INTO r USING t;
dbms_output.put_line('test:'||r);

END sp_test;
/

ORA-00903: invalid table name
ORA-06512: at "TGTMIT.SP_TEST", line 9
ORA-06512: at line 2

Hi

I have this code

declare
l_sql long;
l_table varchar2(30) := 'EMP';
l_ename varchar2(10);
begin
l_sql := 'select ename from '||l_table||' where ename = ''KING''';
execute immediate l_sql into l_ename;
dbms_output.put_line('['||l_ename||']');
end;
/

I sql traced the session using 10046 event then I see in the trace file this SQL

SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE
NO_PARALLEL(SAMPLESUB) NO_PARALLEL_INDEX(SAMPLESUB) */ NVL(SUM(C1),0),
NVL(SUM(C2),0)
FROM
(SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("EMP") FULL("EMP")
NO_PARALLEL_INDEX("EMP") */ 1 AS C1, CASE WHEN "EMP"."ENAME"='KING' THEN 1
ELSE 0 END AS C2 FROM "EMP" "EMP") SAMPLESUB

Why is this query used internally by Oracle? It turns out that when using dynamic SQL I am actually querying twice the table!

Hi

declare
l_sql long;
l_table varchar2(30) := 'EMP';
l_ename varchar2(10);
begin
l_sql := 'select ename from '||l_table||' where ename = ''KING''';
execute immediate l_sql into l_ename;
dbms_output.put_line('['||l_ename||']');
end;
/

I cant use bind variable because the table_name is dynamic. Or is there any way which I dont know :-?

And yes, the table didnt have any stats....

cheers

Tom,
Please see the quote from PL/SQL User Guide and Reference page 11-15 (Native Dynamic SQL)
=================================================
Using Duplicate Placeholders
Placeholders in a dynamic SQL statement are associated with bind arguments in the USING clause by position, not by name. So, if the same placeholder appears two or more times in the SQL statement, each appearance must correspond to a bind
argument in the USING clause. For example, given the dynamic string
sql_stmt := ’INSERT INTO payroll VALUES (:x, :x, :y, :x)’;
you might code the corresponding USING clause as follows:
EXECUTE IMMEDIATE sql_stmt USING a, a, b, a;
However, only the unique placeholders in a dynamic PL/SQL block are associated
with bind arguments in the USING clause by position. So, if the same placeholder
appears two or more times in a PL/SQL block, all appearances correspond to one
bind argument in the USING clause. In the example below, the first unique
placeholder (x) is associated with the first bind argument (a). Likewise, the second
unique placeholder (y) is associated with the second bind argument (b).
DECLARE
a NUMBER := 4;
b NUMBER := 7;
BEGIN
plsql_block := ’BEGIN calc_stats(:x, :x, :y, :x); END;’
EXECUTE IMMEDIATE plsql_block USING a, b;
...
END;
=========================================
I am doing as

create table t1(
id_pmt number(10),
id_en number(10),
id_pay number(10));

Then write an anonymous PL/SQL block:

declare
v_Query varchar2(100);
inv number(10);
pmt number(10);
begin
v_Query := 'Select * from t1 where id_pmt=:x and id_en=:y and id_pay=:y';

Execute immediate v_Query using INV,PMT;

end;

I get ora-01008 not all variables bound error.

If I change v_Query to:
v_Query := 'Select * from t1 where id_pmt=:x and id_en=:y';

then the block works fine.

The database is 9.2.0.5. How can I make it work like mentioned in the Oracle documentation?

Thanks

Hi

In 8i we had seperate engine for SQL and PL/SQL, in 9i and 10g they are merged into one.

In PRO*C however to use analtyic functions in 8i, 9i ad 10g we still need to use dynamic SQL. My question is, what engines does PRO*C use?

Cheers

Hi

It seems that PRO*C is not being improved by the vendor, in this case Oracle. How so? PRO*C has stayed in the same technology track since 1997 or so! No improvements at all and the future seems dark as well.
PL/SQL is cool but for massive data processes such as billing application are much faster in C than pure PL/SQL.

Cheers

create table x (a number);

begin
for a in 1..5
loop
insert into x values (a);
end loop;
end;
/

create procedure test as
b number := 10;
begin
insert into x(a)
select b
from (select b
from x
where a = 2);
end;
/

create package test1 as
procedure ins(z number) ;
end;
/

create package body test1 as
procedure ins(z number) is
b number := 20;
begin
insert into x(a)
select b
from (select b
from x
where a = z);
end;
end;
/

begin
test;
test1.ins(1);
test1.ins(2);
end;
/





DDMMGR@DTSTGDOD> select * from x ;

A
----------
1
2
3
4
5
10
1
2

8 rows selected.

in case of package why did it insert 1 & 2 where as in procedure it inserted 10 ??

what changes you needed to do in order to make this work ?? On my database, this runs without any issues. and as you see the output, it is all weird.

SQL>
SQL> begin
  2      test;
  3      test1.ins(1);
  4      test1.ins(2);
  5  end;
  6  /
begin
*
Error in line 1:
ORA-00904: "B": invalid identifier
ORA-06512: at "TESTER.TEST", line 4
ORA-06512: at line 2

 

I have created a package with three functions. They return Strings as CREATE OR REPLACE for a package, an insert trigger and an update trigger. I pass the table name and schema name of the table I wish to audit and return the string to create the package. I create package variables, one for each column in the table and also create an insert statement. To do this I have a cursor that selects from all_tab_columns.

This works if I run it from the schema that owns the table, but the cursor returns no records if I run it from another schema. I can run the query in the other schema in SQLPlus and get the correct records. The grantee of all_tab_columns is public which seems to appear as a direct grant.

My workaround is to run the functions in the table owners schema. Can you give any insight as to why a cursor selecting column_name from all_tab_columns would only return records where the table is owned by that schema when run in a procedure or anonymous block and yet return more records when run in SQLPlus?

Interested in Boston

u no wut i mean? [just kidding on the IM speak. I agree with you]

I have a situation to change the password of other user using a stored procedure.

Login as user A using sqlplus. Execute a stored procedure that changes the password of user B. The old password and the new password along with the username are the parameters of the stored proc.

This is what I did:

create or replace procedure change_others_password(p_user_id in varchar2,
p_old_password in varchar2,
p_new_password in varchar2)
as
v_sql varchar2(1000);
v_user varchar2(30);
begin

select username into v_user
from dba_users
where username=sys_context('userenv','session_user');

dbms_output.put_line('before user '||v_user);

v_sql := 'connect '||p_user_id||'/'||p_old_password||'@estrp';
dbms_output.put_line(v_sql);
execute immediate v_sql;

select username into v_user
from dba_users
where username=sys_context('userenv','session_user');

dbms_output.put_line('after user '||v_user);

v_sql := 'alter user '||p_user_id||' identified by '||p_new_password||' replace '||p_old_password||';';
--dbms_output.put_line(v_sql);
execute immediate v_sql;
end;

I get the error right at connect to the user B.

Do you a better idea how to achieve this using a stored Proc?

thanks,

I have a situation to change the password of other user using a stored procedure.

Login as user A using sqlplus. Execute a stored procedure that changes the password of user B. The old password and the new password along with the username are the parameters of the stored proc.

This is what I did:

create or replace procedure change_others_password(p_user_id in varchar2,
p_old_password in varchar2,
p_new_password in varchar2)
as
v_sql varchar2(1000);
v_user varchar2(30);
begin

select username into v_user
from dba_users
where username=sys_context('userenv','session_user');

dbms_output.put_line('before user '||v_user);

v_sql := 'connect '||p_user_id||'/'||p_old_password||'@estrp';
dbms_output.put_line(v_sql);
execute immediate v_sql;

select username into v_user
from dba_users
where username=sys_context('userenv','session_user');

dbms_output.put_line('after user '||v_user);

v_sql := 'alter user '||p_user_id||' identified by '||p_new_password||' replace '||p_old_password||';';
--dbms_output.put_line(v_sql);
execute immediate v_sql;
end;

I get the error right at connect to the user B.

Do you a better idea how to achieve this using a stored Proc?

thanks,

I am trying to accomplish to change the password of the other user.  If you want to change the password of a user, you log in as that user, change the password and exit out.

SQL> show user;
abc

SQL> connect xyz/oracle@estrp;
connected:

SQL> alter user xyz identified by new_oracle replace oracle;

SQL> disconnect;

Here I logged in as abc (initially), then connected as xyz, changed the password of xyz and disconnected from xyz.

I want to accomplish something like this using a stored proc.  The stored proc is owned by user abc.  The parameters I supply to my stored proc is user(xyz), old password(oracle) and new password (new_oracle).  It should change the password of the user xyz and exit out.

Is it possible to accomplish?
If so how?

thanks,
 

If the user A has grants to change the password of user B, then how does the stored proc looks like?

Let me try to understand your explanation clearly:
If user A does not have grants to change the password of user B, then there is no way using a stored proc to login as user B (from user A), change the password of user B (here I am changing the password of user B being user B NOT user A) and then exit out.

Thanks,

If you want A to be able to change B's password (although, that seems "strange" doesn't it?), it could look like this:


ops$tkyte@ORA9IR2> create user a identified by a;
User created.
 
ops$tkyte@ORA9IR2> create user b identified by b;
User created.
 
ops$tkyte@ORA9IR2> grant create session to a;
Grant succeeded.
 
ops$tkyte@ORA9IR2> grant create session, create procedure to b;
Grant succeeded.
 
ops$tkyte@ORA9IR2> @connect b/b
ops$tkyte@ORA9IR2> set termout off
b@ORA9IR2> set termout on
b@ORA9IR2> create procedure change_b( p_new_password in varchar2 )
  2  as
  3          l_whitespace varchar2(20) := ' ' || chr(10) || chr(13) || chr(9);
  4  begin
  5          if ( p_new_password = replace( translate(p_new_password,l_whitespace,' '), ' ', '' ) )
  6          then
  7                  execute immediate 'alter user b identified by ' || p_new_password;
  8          else
  9                  raise_application_error( -20001, 'no way' );
 10          end if;
 11  end;
 12  /
Procedure created.
 
b@ORA9IR2> grant execute on change_b to a;
Grant succeeded.
 
b@ORA9IR2> @connect a/a
b@ORA9IR2> set termout off
a@ORA9IR2> set termout on
a@ORA9IR2>
a@ORA9IR2> exec b.change_b( 'newpassword' );
 
PL/SQL procedure successfully completed.
 
a@ORA9IR2> connect b/b
ERROR:
ORA-01017: invalid username/password; logon denied
 
 
Warning: You are no longer connected to ORACLE.
a@ORA9IR2> connect b/newpassword
Connected.
 

I appreciate your time for answering my question.

When does one require to use the keyword "replace" when issuing "alter user" command to change the password?

Is this mandatory on some situation?

thanks,

HOw can I do the above? I am trying to find it in your
website, but I can't.

Hi Tom,
I have a situation where i have to keep the value of the column name in some variable and then prepare a sql statemnt and do Execute Immediate to get the value of the column.I have done in this way ,but i am not getting the correct result.What is the way around

column_name :=DM_NHS_NUMBER; --this is coming through some logic.
sql_stmt1 :='SELECT :ID1 FROM PMI_STAGING WHERE DM_SEQ =:dm_seq1';
EXECUTE IMMEDIATE sql_stmt1 INTO column_value USING COLUMN_NAME,DM_SEQ1;

Hi Tom

reading this thread, I notice on several occaisions you say
DDL in stored procedures is not a good idea.

Can you give some reasons why ?

thanks

Syed

Tom,
Need your expertise on my situation.

We currently wrote some packages[which has procedures with same functionality, in all the packages].

eg: package 1:
Procedure DATAOUT (inp IN integer,
DataOut OUT sys_refcursor)
is

Begin

Open DataOut as Select [List ofcolumns] from [table_name] where id=inp;

End;

Function InsertData(list of columns as IN parameters)
return integer
is
v_id in integer:=0;
Begin
Insert into table_name[columns]..values [list of parameters] returning id into v_id;
return v_id
exception
[exception handling]
return 0;
end;


eg: package 2:
Procedure DATAOUT (inp IN integer,
DataOut OUT sys_refcursor)
is

Begin

Open DataOut as Select [List ofcolumns] from [table_name] where id=inp;

End;

Function InsertData(list of columns as IN parameters)
return integer
is
v_id in integer:=0;
Begin
Insert into table_name[columns]..values [list of parameters] returning id into v_id;
return v_id
exception
[exception handling]
return 0;
end;

Each package is written for each Table (object from front end);

We are thinking to Generalise all the functions instead of repeating same functionality for every object (in every package).
But if I want to do that, then i have to
1) Make the static SQL's as Dynmaic SQL's (as input parameters for InsertData function will be an Array,with table name passed as another parameter) and construct column values from that array.
2) And to find columns dynamically for each table, we need to access user_tab_columns and get column_names from it.

My Question is , is it really worth doing this way , or just keep everything like we currently have and get on with it? Can you explain what's the best approach [with valid reasons..much better]

I remember reading from your responses , you prefer keeping it as Static SQL's then dynamic unless required.

Thanks for any comments you have on this,

Tom,
Thanks for your reply. I told my group that we are not Generalizing.
But I did not get points
a) dependency mechanism -- gone
b) ability for code to be self healing when you modify
the base table -- gone

Can you eloborate on this.

Thanks a lot.

Hi Tom,

I have a user named app_owner who owns certain packages that are also used by another user named 'puja'.

I want 'puja' to check the status of packages that she is using(belonging to app_owner) and compile them if they are found 'invalid'. We don't wish to give any privileges directly to 'puja' and hence I created the following procedure as 'app_owner'

SQL> show user
USER is "APP_OWNER"
SQL> ed
Wrote file afiedt.buf

  1  CREATE OR REPLACE PROCEDURE CHK
  2  AS
  3  STR VARCHAR2(100);
  4  CURSOR C IS select object_name, status from USER_objects where OBJECT_TYPE LIKE 'PACKAGE%';
  5  BEGIN
  6  FOR I IN C LOOP
  7  IF I.STATUS='INVALID' THEN
  8  STR:='ALTER PACKAGE '||I.OBJECT_NAME||' COMPILE';
  9  EXECUTE IMMEDIATE STR;
 10  END IF;
 11  END LOOP;
 12* END;
SQL> /

Procedure created.

SQL> grant execute on chk to puja;

Grant succeeded.

SQL> conn puja/puja@rdb
Connected.
SQL> exec app_owner.chk;
ERROR:
ORA-24344: success with compilation error
ORA-06512: at "APP_OWNER.CHK", line 9
ORA-06512: at line 1



Warning: PL/SQL compilation errors.

SQL> show err
No errors.


I thought the problem is arising because I had not appended schema name to the package name. so to test it, I went back to app_owner, but on executing the procedure, i got the same error:

SQL> show user
USER is "APP_OWNER"
SQL> exec chk;
ERROR:
ORA-24344: success with compilation error
ORA-06512: at "APP_OWNER.CHK", line 9
ORA-06512: at line 1



Warning: PL/SQL compilation errors.

SQL> show err
No errors.
SQL>

Can you please tell me where I went wrong?

Thanks and regards

Puja 

Hi,

Now I am able to execute the procedure 'chk' without any errors. But it is not compiling the packages (which it is supposed to do)

I have a package 'test' that is containing reference to the table 'abc' in the same schema.

Initially 'test' is valid.
SQL> select object_name, status from USER_objects where OBJECT_TYPE LIKE 'PACKAGE%';

OBJECT_NAME          STATUS
-------------------- -------
BASE_RATE_UPDATE_PKG VALID
BASE_RATE_UPDATE_PKG VALID
DATAPORTING          VALID
DATAPORTING          INVALID
............


TEST                 VALID
TEST                 VALID


.............

Now I alter the structure of 'abc'. It invalidates the package 'test'

SQL> alter table abc add (z number);

Table altered.

SQL> select object_name, status from USER_objects where OBJECT_TYPE LIKE 'PACKAGE%';

OBJECT_NAME          STATUS
-------------------- -------
BASE_RATE_UPDATE_PKG VALID
BASE_RATE_UPDATE_PKG VALID
DATAPORTING          VALID
DATAPORTING          INVALID
.....
TEST                 VALID
TEST                 INVALID
......


I execute the procedure chk (which has to check what all packages are invalid and then compile them)

SQL> exec chk;

PL/SQL procedure successfully completed.

SQL> select object_name, status from USER_objects where OBJECT_TYPE LIKE 'PACKAGE%';

OBJECT_NAME          STATUS
-------------------- -------
BASE_RATE_UPDATE_PKG VALID
BASE_RATE_UPDATE_PKG VALID
DATAPORTING          VALID
DATAPORTING          INVALID
.......
TEST                 VALID
TEST                 INVALID
.................

But on explicitly giving the compile statement on SQL prompt, the package status becomes VALID:

SQL> alter package test compile;

Package altered.

SQL> select object_name, status from USER_objects where OBJECT_TYPE LIKE 'PACKAGE%';

OBJECT_NAME          STATUS
-------------------- -------
BASE_RATE_UPDATE_PKG VALID
BASE_RATE_UPDATE_PKG VALID
DATAPORTING          VALID
DATAPORTING          INVALID
.........

TEST                 VALID
TEST                 VALID

Why does the ALTER PACKAGE --- COMPILE; doesn't work inside the procedure?

Regards,

Puja 

HI Tom,

The problem is solved now.

Thanks

Puja

Hello Tom,

I'm facing a problem trying to use "Execute immediate" in a procedure. 
What I'm trying to do is to list a first table (dept) and with the colum result of this (loc) I try to do a select * of tables wich names are in loc.
I try to do this using an array in the Execute immediate statement but I get the following error:

Procedure
=======================================================
create or replace procedure proc_startup  is
    str     varchar2(200);
    str1    varchar2(200);
    type    my_curs_type is REF CURSOR;  
    curs    my_curs_type;
    ret     varchar2(200);
    type    string_tab is table of varchar2(20);
    ret_tab string_tab;
begin
    str := 'select loc from dept';
    DBMS_OUTPUT.PUT_LINE(str);
    OPEN curs FOR str;
    FETCH curs BULK COLLECT INTO ret_tab;
    dbms_output.put_line('Table names: ');
    for i in 1..curs%rowcount loop
       dbms_output.put_line(str); 
       execute immediate 'select * from ' || i.ret_tab;
    end loop;
  CLOSE curs;
end;
=======================================================
When I run this I get a compilation error:

SQL> @c:/proc_startup_examp.sql
 33  /

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE PROC_STARTUP:

LINE/COL ERROR                                                                  
-------- --------------------------------------------------     
27/8     PL/SQL: Statement ignored                                              
27/48    PLS-00487: Invalid reference to variable 'I' 

==========================================================
When I change the Execute immediate statement for this:

   execute immediate 'select * from ' || ret_tab(i);

the procedure compiles without errors but I get the followoing error when trying to execute the procedure:

SQL> @c:/proc_startup_examp.sql
 33  /

Procedure created.

SQL> show errors
No errors.
SQL> exec proc_startup
select loc from dept                                                            
BEGIN proc_startup; END;

*
ERROR at line 1:
ORA-00942: table or view does not exist 
ORA-06512: at "SYSTEM.PROC_STARTUP", line 17 
ORA-06512: at line 1 
-----------------------------------------------------
PLEASE tell me what am I doing wrong and how can I correct my procedure.

The result that I'm looking for is:

select loc from dep => T1, T2, T3

select * from loc => select * from T1;
                     select * from T2;
                     select * from T3;

and overall that these last select's get actually executed!

Thank you so much for your advice!

Regards,

 

 

Hi again,

Thank you for your answer. Would you be so kind to give me an example that i can use? Thanks again!

Regards.

Hi Tom,

The problem is seems simple but I'm not getting the answer here and also couldn't get any answer from this forum too. The question which I described thru the PL/SQL block where I wants to pass row%type attribute dynamically into dbms_output.put_line funtion to display. Is there such way similar to Dynamic PL/SQL it can be acomplished.

Correct me if I'm wrong.

Thanking you for your kind help and time.

Regards,
Suvendu


---------------------------

drop table t;

create table t as select * from all_objects where rownum<=5;

declare
t_row t%rowtype;
v_tot_col pls_integer:=0;
v_col_name varchar2(30);
v_dynamic_col varchar2(40);
begin
select * into t_row from t where rownum=1;

select count(1) into v_tot_col from user_tab_columns where table_name='T';

for i in 1.. v_tot_col
loop
select column_name into v_col_name from user_tab_columns
where table_name='T' and column_id=i;

v_dynamic_col:=t_row||'.'||v_col_name;

dbms_output.put_line(v_col_name||' = '||v_dynamic_col);
end loop;

end;

Sorry, don't know how to ask question the other way...

Have the problem like this: freely dropping and altering schema objects through dynamic sql, but can't create any new - insufficient privilegies error. What's interesting, this error I have only in named blocks (procedures, functions etc.), anonymous ones are running freely. Adding AUTHID CURRENT_USER solves the problem, but leaves me a little confused. Some privilegies are granted through roles, maybe this causes the problem? Oracle 9
Any links to the documentation on this cause will be greatly appreciated.

My best regards...

How can I miss such a thing?.. Next time will be more attentive, when reading documentation.

Hi Tom,

I am writing the procedure for Rolling Windows partitioning and trying to add a new partition and simultaneously drop the oldest partion.

I am dynamically preparing a statement and then using execute immediate trying to execute it, but it is failing

The statement so constructed is like this

add_partition_string := '''' || 'Alter table ' || tables.table_name || ' add partition ' ||  p_partition_new_name
     || ' VALUES LESS THAN (to_date(' || '''' || to_char(p_partition_new_date,'DD/MM/YYYY hh24:mi:ss') || '''' || ',' || '''' ||'DD/MM/YYYY hh24:mi:ss' || '''' || '))' || '''' ;

'Alter table INVOICES add partition P_20010106 VALUES LESS THAN
(to_date('07/01/2001 00:00:00','DD/MM/YYYY hh24:mi:ss'))'

but when I say:

execute immediate add_partition; it gives me an error:
ORA-00900: invalid SQL statement

However from SQL> prompt the statement is getting executed.

Please let us know why it is throwing the error

Thanks

 

I solved my very critical problem with the questions and responses in this forum.

Thanks a lot Tom

Joshua

Hi Tom,

Customer Table
Customer Code


Automobile
Customer Code
Product Code
Date of Purchase
Cost
Cultural
Customer Code
Product Code
Date of Purchase
Cost
Ornaments
Customer Code
Product Code
Date of Purchase
Cost
Cloths
Customer Code
Product Code
Date of Purchase
Cost

Like this i have 10 product tables, in all these 3 columns are common
i want to know Customer wise how much amount he has purchased on a given date........
I have written a Proc where i have put 10 queries to get sum of cost from all tables separately and calculated total
Is there any other method to get this result.....
it is not that a customer should buy all the products, he may buy product1 or Product 1 to 5 ...

Thanx in Adv.

Sanjeev Vibuthi

Thanks Tom

Dear Tom,

scott@testdb10gR2-09-FEB-06>Create Table FIndex (Dcd Number(3), Ucd Number(3),
2 Fno Varchar(9), Mh Number(3), Mi Number(3),
3 stage char(3))
4 /

Table created.

scott@testdb10gR2-09-FEB-06>Create Table Stmt1 (Slno Number, Mh Number(3), Mi Number(3))
2 /

Table created.

scott@testdb10gR2-09-FEB-06>Create Table MM_Master (Mh Number(3), Mi Number(3))
2 /

Table created.

scott@testdb10gR2-09-FEB-06>Insert into MM_Master values (1,10);
scott@testdb10gR2-09-FEB-06>Insert into MM_Master values (1,11);
scott@testdb10gR2-09-FEB-06>Insert into MM_Master values (1,12);
scott@testdb10gR2-09-FEB-06>Insert into MM_Master values (1,13);
scott@testdb10gR2-09-FEB-06>Insert into MM_Master values (2,21);
scott@testdb10gR2-09-FEB-06>Insert into MM_Master values (2,22);
scott@testdb10gR2-09-FEB-06>Insert into MM_Master values (3,31);
scott@testdb10gR2-09-FEB-06>Insert into MM_Master values (4,41);
scott@testdb10gR2-09-FEB-06>Insert into MM_Master values (4,42);
scott@testdb10gR2-09-FEB-06>Insert into MM_Master values (4,43);

scott@testdb10gR2-09-FEB-06>Insert into findex values (22,46,'1/2005',1,10,'UI');
scott@testdb10gR2-09-FEB-06>Insert into findex values (22,46,'2/2005',1,11,'PT');
scott@testdb10gR2-09-FEB-06>Insert into findex values (22,46,'3/2005',1,13,'UI');
scott@testdb10gR2-09-FEB-06>Insert into findex values (22,46,'4/2005',1,12,'CHD');
scott@testdb10gR2-09-FEB-06>Insert into findex values (22,46,'5/2005',2,21,'UI');
scott@testdb10gR2-09-FEB-06>Insert into findex values (22,46,'6/2005',2,22,'PT');
scott@testdb10gR2-09-FEB-06>Insert into findex values (22,46,'7/2005',1,10,'UI');
scott@testdb10gR2-09-FEB-06>Insert into findex values (22,46,'8/2005',4,41,'PT');
scott@testdb10gR2-09-FEB-06>Insert into findex values (22,46,'9/2005',4,42,'UI');

scott@testdb10gR2-09-FEB-06>Insert into stmt1 values (2,1,11);
scott@testdb10gR2-09-FEB-06>Insert into stmt1 values (3,1,0);
scott@testdb10gR2-09-FEB-06>Insert into stmt1 values (4,2,21);
scott@testdb10gR2-09-FEB-06>Insert into stmt1 values (5,2,0);
scott@testdb10gR2-09-FEB-06>Insert into stmt1 values (6,3,0);
scott@testdb10gR2-09-FEB-06>Insert into stmt1 values (7,4,41);
scott@testdb10gR2-09-FEB-06>Insert into stmt1 values (8,4,42);

scott@testdb10gR2-09-FEB-06>select * from findex;

DCD UCD FNO MH MI STA
---------- ---------- --------- ---------- ---------- ---
22 46 1/2005 1 10 UI
22 46 2/2005 1 11 PT
22 46 3/2005 1 13 UI
22 46 4/2005 1 12 CHD
22 46 5/2005 2 21 UI
22 46 6/2005 2 22 PT
22 46 7/2005 1 10 UI
22 46 8/2005 4 41 PT
22 46 9/2005 4 42 UI

9 rows selected.


scott@testdb10gR2-09-FEB-06>select * from mm_master;

MH MI
---------- ----------
1 10
1 11
1 12
1 13
2 21
2 22
3 31
4 41
4 42
4 43

10 rows selected.

scott@testdb10gR2-09-FEB-06>select * from stmt1;

SLNO MH MI
---------- ---------- ----------
1 1 10
2 1 11
3 1 0
4 2 21
5 2 0
6 3 0
7 4 41
8 4 42

8 rows selected.


scott@testdb10gR2-09-FEB-06>select mf.mh,stage,count(*) from
2 findex mf,
3 stmt1 st
4 where
5 st.mh=mf.mh and
6 (
7 st.mi<>0 and -- if mi value is <>0, select mi value from stmt1 table
8 mf.mi in
9 (select s2.mi from stmt1 s2
10 where s2.mh=mf.mh and st.slno=s2.slno)
11 ) OR
12 (
13 st.mi=0 and -- if mi value is 0, select mi value from mm_master table
14 mf.mi in
15 (select s3.mi from mm_master s3 where
16 s3.mh=mf.mh)
17 )
18 group by mf.mh,stage
19 /

MH STA COUNT(*)
---------- --- ----------
2 PT 3
1 PT 4
1 UI 11
2 UI 4
4 PT 4
4 UI 4
1 CHD 3

7 rows selected.

I got the result but MH, Stages are correct but Count is more than what it is there...

Can i give a sub query in where like this?

Thanx in Adv

Sanjeev Vibuthi

Hi Tom,

I am waiting for ur reply.. becuase this query can solve my critical problems in some of major reports....

Whether this "type" of queries are available in any of your books.. ( I Have Expert.. and Effective... books) or I have to write a Proc and any other link which have similar queries in this site...

Thankx in Adv.

Sanjeev Vibuthi

Sorry Tom,

Plz Dont mind to remind you again about my question... my necessity makes me to do so....

I thought that it is self explanatory ....

Now I'll explain what i required is...

I have a main table "findex", this table contains a FNO (File No) (DCD - Dist Code, UCD - Unit Code, FNO - Composite PK), MH (Main Head), MI (Minor Head), Current Stage of File..

My Actual Requirement is the following :
Stages
SLNO MH MI Description UI PT CHD
---------- ---------- ----------------------------------------------------------------------------------------------------------
1 1 10 Cycle Theft 1 1 0
2 1 11 3 Wheeler Theft 0 0 3
3 1 0 Automobile Theft 0 1 1
4 2 21 House Robbery 1 1 1
5 2 0 Robbery 2 2 0
6 3 0 Accident 2 2 1
7 4 41 Child Missing 3 1 2
8 4 42 Man Missing 2 1 3
9 4 0 Missing 2 1 3

In the final result "MI" column no need to display

MH - 1 means "Automobile Theft" under this MH, 10 - "Cycle Theft", 11 - "3 Wheeler Theft"
slno 1 contains Cycle Theft Stages (group by MH, Mi)
slno 2 contains 3 Wheeler Theft (group by MH, Mi)
slno 3 contains Total Automobile theft (group by MH)

In "Stmt1" table I am inserting slno, Mh, Mi, Description
If query i am checking it is is MI is 0, I am taking all MI for MH from MM_Master table (so i can get total under MH), if it is >0, I am taking only MI value from "Stmt1" table

select mf.mh,stage,Sum(Decode(stage,'UI',1,0)) "UI",
Sum(Decode(stage,'PT',1,0)) "PT",
Sum(Decode(stage,'CHD',1,0)) "CHD" from
findex mf,
stmt1 st
where
st.mh=mf.mh and
(
st.mi<>0 and
mf.mi in
(select s2.mi from stmt1 s2
where s2.mh=mf.mh and st.slno=s2.slno)
) OR
(
st.mi=0 and
mf.mi in
(select s3.mi from mm_master s3 where
s3.mh=mf.mh)
)
group by mf.mh,stage
/

With this query i will get MH, stage wise count, same will be updated in Stmt1 table and displayed


Sanjeev Vibuthi

Hi Tom,

Actually I want (A AND B) OR (A AND C)

SCOTT@ testdb 13-FEB-06>Select * from dual where (1=1 and 2=2) OR (1=1 and 3=3);

D
-
X

Initally it was like this ...
Declare
--- If both mh and mi are present
CURSOR c1 (v_mh NUMBER, v_mi NUMBER) IS
Select Sum(Decode(mh,v_mh, decode(mi,v_mi,
Decode(stage,'UI',1,0)))) "UI",
Sum(Decode(mh,v_mh, decode(mi,v_mi,
Decode(stage,'CHD',1,0)))) "CHD",
Sum(Decode(mh,v_mh, decode(mi,v_mi,
Decode(stage,'PT',1,0)))) "PT"
from findex
where mh=v_mh and
mi=v_mi;

--if only mh is present
CURSOR c2 (v_mh NUMBER) IS
Select Sum(Decode(mh,v_mh, Decode(stage,'UI',1,0))) "UI",
Sum(Decode(mh,v_mh, Decode(stage,'CHD',1,0))) "CHD",
Sum(Decode(mh,v_mh, Decode(stage,'PT',1,0))) "PT"
from findex
where mh=v_mh;

CURSOR c3 IS
SELECT * from stmt1;

rec1 c1%ROWTYPE;
rec2 c2%ROWTYPE;
rec3 c3%ROWTYPE;

Begin

Open c3;
Loop
Fetch c3 into rec3;
Exit when c3%notfound;

If rec3.mh<>0 AND rec3.mi<>0 then --If both are present
Open c1 (rec3.mh,rec3.mi);
Fetch c1 into rec1;
elsif rec3.mh<>0 AND rec3.mi=0 then -- if mh is present
Open c2 (rec3.mh);
Fetch c2 into rec2;
end if;
If c1%isopen then
dbms_output.put_line(' Both '||rec3.mh ||' - '|| rec3.mi ||' ='|| rec1.UI||'-'||rec1.PT||'-'||rec1.CHD);
else
dbms_output.put_line(' Only MH '||rec3.mh ||' ='|| rec2.UI||'-'||rec2.PT||'-'||rec2.CHD);
end if;
if c1%isopen then
close c1;
else
close c2;
end if;
end loop;
close c3;
end;
/

Now i want to make it in a single cursor (single query)...

I have changed the query as follows, but result count is not matching again...

select mf.mh, Sum(Decode(stage,'UI',1,0)) "UI",
Sum(Decode(stage,'PT',1,0)) "PT",
Sum(Decode(stage,'CHD',1,0)) "CHD" from
findex mf,
stmt1 st
where
( st.mh=mf.mh
and
st.mi<>0 and
mf.mi in
(select s2.mi from stmt1 s2
where s2.mh=mf.mh and st.slno=s2.slno)
)
OR
( st.mh=mf.mh and
st.mi=0 and
mf.mi in
(select s3.mi from mm_master s3 where
s3.mh=mf.mh)
)
group by mf.mh
/

Thanx in adv.

Sanjeev Vibuthi

hmmmm...

Thanx tom ... gr8 help .... it almost solved my problem...

In sql server i can write update statement like this :

Update emp set
ename=e1.ename,
sal=e1.sal
from emp, emp1 e1
where emp.empno=e1.empno

How can I Write the same statement in Oracle... It looks more convinient

Sanjeev Vibuthi

Excellent...

Thanx Tom... Really ur gr8...

Hi Tom

I have a database procedure that I need to test. Its definition is as under:

PROCEDURE test( p1 VARCHAR2,
p2 VARCHAR2,
p3 VARCHAR2,
p4 VARCHAR2,
p5 VARCHAR2,
p6 VARCHAR2,
p7 VARCHAR2,
p8 VARCHAR2 )
I need to test all the possible combinations of parameter values. For e.g. one test will be to just pass parameter p1. Another test will be to pass parameters p1 and p2 and so on. Is there any way to construct data source dynamically?

Thanks

Hi,

I have to create a dynamic sql in my pl/sql code, and I want to get results into my variable.

How can I do this ?

Thanks

Jonty

Hi Tom,

not sure if this is the best place to ask this .. but it seemed somewhat related ... I did some searching, and couldn't find any other places ..

This works using Explicit Cursor ...
================
set serverout on

DECLARE
lv_var1 NUMBER;

TYPE my_cur_type IS REF CURSOR;
my_cur my_cur_type;

c_loop NUMBER;
BEGIN

IF TRUE THEN -- ie some condition ...
OPEN my_cur FOR 'SELECT 1 abc FROM dual';
ELSE
OPEN my_cur FOR 'SELECT 2 abc FROM dual';
END IF;

LOOP
FETCH my_cur INTO c_loop;
EXIT WHEN my_cur%NOTFOUND;
lv_var1 := c_loop;
END LOOP;

CLOSE my_cur;

dbms_output.put_line ( lv_var1 );
END;
/
========================

Basically, it opens 1 of 2 cursors based on a decision (fixed for this example ...) fetches from it, and exits.

What I am curious about, however, is this:

Is there a way to accomplish the same (open 1 of 2 or more cursors based on a decision) using implicit cursors:

For example (this doesn't work ... but something like it would be nice?)
===============
set serverout on

DECLARE
lv_var1 NUMBER;

my_cur VARCHAR2(1000);

BEGIN

IF TRUE THEN -- ie some condition ...
my_cur := 'SELECT 1 abc FROM dual';
ELSE
my_cur := 'SELECT 2 abc FROM dual';
END IF;

FOR c_loop IN my_cur
LOOP
lv_var1 := c_loop;
END LOOP;

dbms_output.put_line ( lv_var1 );
END;
/
==============
Or even if there's a way to define the cursors in the declare block via:

cursor cur_1 is 'select 1 abc from dual';
cursor cur_2 is 'select 2 abc from dual';

.. then do either:
FOR c_loop IN cur_1
or
FOR c_loop IN cur_2

.. ???
Is this even possible using the FOR .. IN ... LOOP structure?
If so .. how do I pull this off?
(Yes, 10g ...)

Hi Tom,
Could you please, look into below few statements? 

Is there different behaviour of function having dynamic sql inside it, executing with EXEC command vs. calling with SELECT statement?

Kindly, currect me if I'm wrong.

Thanking you.

Regards,
Suvendu

-----------------------


M:\>sqlplus "scott/tiger"

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Dec 1 18:05:26 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create or replace function fnc_drop_idx(p_index_name varchar2) return numb
  2  as
  3  l_index_name varchar2(30);
  4  begin
  5     select index_name into l_index_name
  6     from user_indexes
  7     where index_name=p_index_name;
  8
  9     if l_index_name is not null then
 10             execute immediate 'drop index '||p_index_name;
 11     end if;
 12     return 0;
 13  exception
 14     when others then
 15             null;
 16     return 1;
 17  end;
 18  /

Function created.

SQL> create index idx_job on emp(job);

Index created.

SQL> var x number
SQL> exec :x:=fnc_drop_idx('IDX_JOB');

PL/SQL procedure successfully completed.

SQL> print x

         X
----------
         0

SQL> create index idx_job on emp(job);

Index created.

SQL> select fnc_drop_idx('IDX_JOB') from dual;

FNC_DROP_IDX('IDX_JOB')
-----------------------
                      1

SQL> create index idx_job on emp(job);
create index idx_job on emp(job)
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object


SQL>

 

I’m agreeing with you on each step you mentioned. What I did just for the test case to do it in short-cut way.
- I need to avoid WHEN OTHERS
- AUTONOMOUS TRANSACTION is NOT more applicable to functions as well as to triggers
- And, SQL*PLUS’s EXECUTE stands for BEGIN…END;

As I’m repeating my question again WHY dynamic SQL work (index dropped) when function called through BEGIN…END; but NOT through SELECT statement?

Once again, kindly correct me if I’m wrong and please clear my doubt.

Thanks,
Suvendu

Yes, I checked out...

I not only read slowly ( :-) ) but also thought your SQL is NOT my SELECT, there was my mistake....

Really, I'm proud to communicate with YOU....

Simply can THANK YOU for billion times.

Regards,
Suvendu


------------------

1  create or replace function fnc_drop_idx(p_index_name varchar2
  2  as
  3  PRAGMA AUTONOMOUS_TRANSACTION;
  4  l_index_name varchar2(30);
  5  begin
  6       select index_name into l_index_name
  7       from user_indexes
  8       where index_name=p_index_name;
  9       if l_index_name is not null then
 10               execute immediate 'drop index '||p_index_name;
 11       end if;
 12       return 0;
 13  exception
 14       when others then
 15               null;
 16       return 1;
 17* end;
SQL> /

Function created.

SQL> create index idx_job on emp(job);

Index created.

SQL> var x number
SQL> exec :x:=fnc_drop_idx('IDX_JOB');

PL/SQL procedure successfully completed.

SQL>
SQL> print x

         X
----------
         0

SQL> create index idx_job on emp(job);

Index created.

SQL>
SQL> select fnc_drop_idx('IDX_JOB') from dual;

FNC_DROP_IDX('IDX_JOB')
-----------------------
                      0

SQL>
 

Tom:

Do you know what is wrong with exec immediate below:

FOR x in (select * from user_Tables order by table_name)
LOOP
htp.p('<TR>');
htp.p('<TD>');
htp.p(nvl(x.table_name,'&nbsp;'));
htp.p('</TD>');
htp.p('<TD>');

exec immediate 'SELECT count(*) from '||x.table_name
into t1_count;


(1): PLS-00103: Encountered the symbol "IMMEDIATE" when expecting one of the following:
(1): := . ( @ % ;
(1): The symbol ":= was inserted before "IMMEDIATE" to continue.

exec is a sqlplus'ism, it is short for execute in sqlplus and in sqlplus 

SQL> execute p

is just shorthand for:

SQL> begin p; end;
  2  /


you wanted to use EXECUTE IMMEDIATE, not "EXEC", as exec doesn't mean anything in the language called plsql 

Tom:

Now it says "invalid" table name in the select. How can that happen when I select table_name from user_tables?

DO you see anything wrong with the query?

This is not really a good idea anyway, but your execute immediate statement is wrong.

SQL> create or replace function f (p_tab in varchar2)
  2  return number as
  3    l_num number;
  4  begin
  5    execute immediate
  6      'begin select count(*) into :x from ' || p_tab || '; end;'
  7      using out l_num;
  8    return l_num;
  9  end;
 10  /

Function created.

SQL> select table_name, f(table_name) from user_tables;

TABLE_NAME                     F(TABLE_NAME)
------------------------------ -------------
INSTRUMENT                                 0
T1                                         8
T2                                         4
T3                                        10
DEPT                                       4
EMP                                       14
BONUS                                      0
SALGRADE                                   5
DUMMY                                      1
PERSON                                     0
ADDRESS                                    0

TABLE_NAME                     F(TABLE_NAME)
------------------------------ -------------
TELEPHONE                                  0
EMAIL                                      0
B                                          0
TEL_TYPE_LOOKUP                            0
TEST_PHONENUMBER                           1
HOLIDAY                                    1
T                                          5
T_ARC                                      2
INVOICE_TABLE                              3

20 rows selected. 

no, that isn't it - the syntax is better than your approach:

ops$tkyte%ORA10GR2> declare
  2          l_cnt number;
  3  begin
  4          execute immediate 'SELECT count(*) from '||'all_users' into l_cnt;
  5          dbms_output.put_line( l_cnt );
  6  end;
  7  /
31

PL/SQL procedure successfully completed.


 

om:

IT was failing at a table name "GROUP. Why does oracle allow that if it is RESERVED WORD.


SQL> select table_name from user_Tables where table_name = 'GROUP';

TABLE_NAME
------------------------------
GROUP

1 row selected.

SQL>select table_name,f_count(table_name) from user_Tables where table_name='GROUP'
                  *
ERROR at line 1:
ORA-06550: line 1, column 36:
PLS-00103: Encountered the symbol "GROUP" when expecting one of the following:
( <an identifier> <a double-quoted delimited-identifier>
table the
The symbol "<an identifier> was inserted before "GROUP" to continue.
ORA-06512: at "DEVADMIN.F_COUNT", line 5
ORA-06512: at line 1
 

Hi Tom

In our system we have a lot of Stored Procedures that are called by the Web Front-End and the SP's pass back ref cursors.

To debug issues it'll be very useful to have a utility procedure that we can put in each of the back-end procedures to store all the parameters that these procedures recieve in each execution.

We can always hard-code with something like :

SELECT 'P_USER_ID => ' || P_USER_ID || 
       ',P_SORT_FIELD => ' || '''' || P_SORT_FIELD || '''' 
       ... 
       into v_params 
FROM DUAL;   

and the store v_params into a table, but as parameters change this piece will have to change as well,

I was wondering by using the user_arguments table we can do this stuff dynamically, so that in each procedure i can add one (or 2 or 3) lines of standard code to do this which does not need to change as parameters change.

Will appreciate any ideas to help do this,

Thanks in advance,
Sunil



======= SOMETHING THAT I TRIED BUT DOES NOT WORK ======
It was easy enough to write a function that returns all
the parameters to a SP -

but when i execute immediate the result of the func below in the main proc it obviously does not work as the parameter name is inside the string !

function arg_sql
(p_package_name    IN VARCHAR2,
 p_proc_name       IN VARCHAR2
) return varchar2 as
CURSOR c  IS
 SELECT argument_name, data_type
 FROM user_arguments aa
 WHERE PACKAGE_NAME = p_package_name
   AND OBJECT_NAME = p_proc_name
   and in_out = 'IN'
 ORDER BY  position;

v_sql varchar2(4000);
v_start varchar2(5);
x number;
BEGIN
 v_sql := 'SELECT ';
 x := 0;
    FOR r IN c
    LOOP
  v_sql := v_sql ||
       case when x = 0 then  '''' else ''',' end ||
     r.argument_name || ' => '' || ' ||
     case
       when r.data_type = 'NUMBER' then r.argument_name
      else ''''''''' || ' || r.argument_name || ' || '''''''''
     end ||
     ' || ';
  x := x + 1;

  END LOOP;

     return v_sql || ''''' FROM DUAL';
END;

the code was not meant to be 'production' code - it was just to indicate use of user_arguments to determine the arguments at run time

in your example to call log_it i'll have to build p_args with the arguments that are passed into the proc - which is precisely i dont want to do

Hi,

I have procedure that creating table using
execute immediate(vc_query);
volume of records 3 milliions.

Dynamic SQL ( CTAS) taking long time . When i run same normal "create table as select * from" statment from SQL*plus its finish in seconds.

I am not sure what causing this problem.

please help.

begin

vc_query := ' create table tb_entity_fund nologging as select /*+PARALLEL( A,8) PARALLEL(B,8) */
b.policy_id, b.item_id, b.product_id, pkg_ls_pm_pub_gl.f_get_entity_fund
(b.product_id,
a.money_id,
pkg_ls_pm_pub_gl.f_get_entity_type (b.policy_id, b.item_id),
pkg_ls_pty_ci.f_get_company_id (a.organ_id)
) v_entity_fund
from t_contract_master a, t_contract_product b
where a.policy_id = b.policy_id and a.liability_state > 0 ';

execute immediate(vc_query);

dbms_output.put_line(' tb_entity_fund created');

exception
when others then
dbms_output.put_line('Error while creating table tb_entity_fund :'||sqlerrm);
raise;
end;

create or replace PROCEDURE PAST_DUE_GET_DATA(mSession_no IN NUMBER,
AS_ON_DATE in date) AS

TYPE GLCurTyp IS REF CURSOR;
GL_cv GLCurTyp ;

sql_stmt varchar2(2000);
gl_tname varchar2(100);
ACCT_NO VARCHAR2(13);
ACC_NAME VARCHAR2(50);
ACC_MATURITY date;
ACC_GRADE VARCHAR2(2);
ACC_ARM_CODE VARCHAR2(3);

begin

SELECT 'CH_GLTRANS_'||TO_CHAR(AS_ON_DATE,'DDMMYY') INTO gl_TNAME FROM DUAL;


SQL_STMT:=' Select C.Account_No,C.SHORT_NAME, C.MATURITY,c.cust_grade,c.arm_code
From '|| gl_TNAME || ' c ' ||
' Where C.in_recd_no <> 2'||
' And C.book_bal < 0'||
' And Substr(C.Account_No,1,3) In ('||'''01A'''||')'||
' And Substr(C.Account_No,5,1) In '||'('||'''I'''||','||'''P'''||','||'''R'''||','||'''V'''||')';

EXECUTE IMMEDIATE SQL_STMT;


--- DBMS_OUTPUT.PUT_LINE(SQL_STMT);
OPEN GL_cv FOR sql_stmt;
LOOP
FETCH GL_cv INTO accT_NO,ACC_NAME,ACC_MATURITY,ACC_GRADE,ACC_ARM_CODE ;
insert into GL_PASTDUE VALUES
(accT_NO,ACC_NAME,ACC_MATURITY,ACC_GRADE,ACC_ARM_CODE,mSession_no );
EXIT WHEN GL_cv%NOTFOUND;
END LOOP;
CLOSE GL_cv;

COMMIT;

END;

Hi Tom,

I am looking for your help on below problem -

I have a stored procedure which accepts three input parameters -
- v_feed_type
- v_dblink
- v_schema

Depending on the value passed query should run against the specific schemas.

Now I have a requirement of bulk insert something like this inside a stored procedure -

insert into error_table(acct_num, position, error_string)
select t1.acct_num,t1.position,decode(t1.home_phone, t2.home_phone, null, 'HomePhoneErr#')||
decode(t1.email_address, t2.email_address, null, 'EmailAddrErr#')||
decode(t1.city, t2.city, null, 'CityErr#') error_string
from v_schema.t1, t2@dblink
where t1.acct_num = t2.acct_num
and t1.feed_type = v_feed_type;


Now problem is I am unable to use "v_schema","v_dblink" and some constant values in dynamic sql.

I tried something like this -

CREATE OR REPLACE PROCEDURE test(feed_type IN VARCHAR2,
dblink IN VARCHAR2,
schema IN VARCHAR2)

AS

BEGIN

EXECUTE IMMEDIATE 'insert into error_table(acct_num, position, error_string)
select t1.acct_num,t1.position,decode(t1.home_phone, t2.home_phone, null, :1)||
decode(t1.email_address, t2.email_address, null, :2)||
decode(t1.city, t2.city, null, :3) error_string
from '||v_schema||'.t1, t2@'||v_dblink||' where t1.acct_num = t2.acct_num and t1.feed_type ='||v_feed_type using 'HomePhoneErr#','EmailAddrErr#','CityErr#';

END;


Could you please help me to find out what is going wrong?

Thanks,
Asim