Question About Dynamic SQL
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Question About Dynamic SQL

  1. #1
    Join Date
    Mar 2010
    Location
    North Carolina, USA
    Posts
    18

    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.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

  3. #3
    Join Date
    Mar 2010
    Location
    North Carolina, USA
    Posts
    18
    Yep I think that's true, which is why I put the :a in the statement.

    Thanks....

  4. #4
    Join Date
    May 2002
    Posts
    2,645
    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.

  5. #5
    Join Date
    Mar 2010
    Location
    North Carolina, USA
    Posts
    18
    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.

  6. #6
    Join Date
    Mar 2010
    Location
    North Carolina, USA
    Posts
    18
    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...

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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
  •  


Click Here to Expand Forum to Full Width