Home » SQL & PL/SQL » SQL & PL/SQL » pass parameters to declaration section (merged)
pass parameters to declaration section (merged) [message #390281] Thu, 05 March 2009 13:06 Go to next message
reachmexyz
Messages: 8
Registered: February 2009
Location: New Jersey
Junior Member
Hello all

i have created a procedure and i want to pass a parameter to declaration section of the procedure.
Procedure is like this

create or replace procedure copycon(schem in varchar2(50))
is
v_col1 schem.tablename.columnname%TYPE
---
---
---
end copycon

i want to pass schema name throuogh parameter schem and pass it to declaration section. But i cannot do it.
Error is "schem is not declared." cant i pass parameters to declatarion section.
If i can what is the best way to do it.
Responses are appreciated.
Re: pass parameters in declaration section [message #390282 is a reply to message #390281] Thu, 05 March 2009 13:11 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
What exactly you tried? Also post the Oracle Exception thrown by the code
Re: pass parameters in declaration section [message #390284 is a reply to message #390281] Thu, 05 March 2009 13:12 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>cant i pass parameters to declatarion section.
can not pass
>If i can what is the best way to do it.
Best way to do what?

I expect you will have all sorts of permission problems continuing in the direction you are going.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: pass parameters to declaration section [message #390287 is a reply to message #390281] Thu, 05 March 2009 13:16 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
do NOT cross/multi-post
Re: pass parameters to declaration section (merged) [message #390288 is a reply to message #390281] Thu, 05 March 2009 13:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't do it.
What is your business need?

Don't multipost your question.

Regards
Michel
Re: pass parameters to declaration section (merged) [message #390292 is a reply to message #390288] Thu, 05 March 2009 14:38 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
SQL> set serveroutput on
SQL> create or replace procedure proc1
  2    (p1 in varchar default null,
  3     p2 in varchar default null,
  4     p3 in varchar := null) is
  5  begin
  6  dbms_output.put_line('P1='||p1||', P2='||p2||', p3='||p3);
  7  end;
  8  /

Procedure created.

SQL> --positional
SQL> begin proc1('AA', 'BB'); end;
  2  /
P1=AA, P2=BB, p3=

PL/SQL procedure successfully completed.

SQL> --named parameters
SQL> begin proc1( p2 => 'BB', p3 => 'CC'); end;
  2  /
P1=, P2=BB, p3=CC

PL/SQL procedure successfully completed.

SQL>
Re: pass parameters to declaration section (merged) [message #390338 is a reply to message #390281] Fri, 06 March 2009 00:55 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
reachmexyz wrote on Thu, 05 March 2009 20:06


i want to pass schema name throuogh parameter schem and pass it to declaration section. But i cannot do it.
Error is "schem is not declared." cant i pass parameters to declatarion section.
If i can what is the best way to do it.
Responses are appreciated.

How would you think this procedure could ever compile? After all, the datatype of the variables is only known at runtime.
This is not possible, and I really am curious for the business need for this.
So, to repeat some of the others: what is the actual problem you are trying to solve?
Previous Topic: Trouble with trigger
Next Topic: ptocedure are stored
Goto Forum:
  


Current Time: Sun Dec 04 06:22:55 CST 2016

Total time taken to generate the page: 0.08908 seconds