-
Question About Dynamic SQL
Hi Folks,
We are using 11.1.0.7 Standard Edition on Linux CentOS 5.4 here.
I am wondering, can dynamic sql be used for sql*plus SET statements? I've searched the documentation on this but have not found anything definitive.
I've tried the following very simple code, but am getting an ORA-00922: missing or invalid option error.
SQL> declare
2
3 v_sql_stmt1 varchar2(100);
4 v_instance_name varchar2(15);
5
6 begin
7
8 select instance_name
9 into v_instance_name
10 from v$instance;
11
12
13 v_sql_stmt1 := 'set sqlprompt :a';
14
15 execute immediate v_sql_stmt1 USING v_instance_name;
16
17 end;
18 /
declare
*
ERROR at line 1:
ORA-00922: missing or invalid option
ORA-06512: at line 15
Thanks for any advice.
-
As far as I remember "USING" specifies bind variables.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Yep I think that's true, which is why I put the :a in the statement.
Thanks....
-
You are confusing PL/SQL with SQL*Plus. SET commands are a feature within SQL*PLus, so how could that be run inside the PL/SQL engine in the database? It has absolutely no relevance or meaning in this context. Before you start a procedure, as in once you are connected via SQL*Plus, run whatever SET commands - which may or may not have any impact on your output. Try running 22/7 within an anonymous block, and set number to some format. See if that has any impact on the output from PL/SQL.
-
Hi,
Interesting observation - I think I know what you mean. Issuing statements to modify the sql*plus display are not considered real sql statements. I did get a different error though:
SQL> declare
2
3 v_number number;
4 v_sql_stmt varchar2(100);
5
6 begin
7
8 select 22 / 7
9 into v_number
10 from dual;
11
12 v_sql_stmt := 'column v_number format 99.99';
13
14 execute immediate v_sql_stmt;
15
16 dbms_output.put_line('The result is: '|| v_number);
17 dbms_output.put_line('The sql_stmt is: '|| v_sql_stmt);
18
19 end;
20 /
declare
*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at line 14
Thanks for your input.
-
Ah ha! I finally found an article on how to do what I wanted to do.
You can add this line to your glogin.sql file:
set sqlprompt "_CONNECT_IDENTIFIER> "
and it will set your sqlprompt to something a tad more useful.
Thanks all...
-
What Stecal is saying is... SQLPlus != PL/SQL; they have a lot of dna in common but are not the same like a monkey and a human are.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|