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.

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.