Home » SQL & PL/SQL » SQL & PL/SQL » concatening a string
| concatening a string [message #37744] |
Tue, 26 February 2002 05:43  |
marco
Messages: 46
Registered: March 2001
|
Member |
|
|
Hi,
I am trying to write a simple scanning proc. that given an input is writing on a table all the code lines holding that input value.
The problem is with the concatenation of the string v_search. Say the input is 'ABC', then v_search should be equal to '%ABC%', but how does one do this simple thing? I'm stuck with PLS-00382 at compile time so far.
Because I coudn't build a string in the declarative part I resorted to use ref cursor as opposed to static, but perhaps this is not necessary, or is it?
One more thing, would it be possible to use in pl/sql a sql+ command such as: spool (filename.sql)?
Thanks.
Marco
CREATE OR REPLACE procedure scan_subpgm(search VARCHAR2) is
-- output
TYPE t_search IS RECORD (
v_owner all_source.owner%type,
v_name all_source.name%type,
v_type all_source.type%type,
v_line all_source.line%type,
v_text all_source.text%type);
v_search t_search;
TYPE c_linescan IS REF CURSOR;
v_linescan c_linescan;
BEGIN
IF search IS NULL THEN
-- if search is 'ABC' v_search should be '%ABC%'
v_search := '''||%||search||%||'''; <---- PLS-00382: wrong expression type
OPEN v_linescan FOR
SELECT owner,name,rtrim(decode(type,'PROCEDURE','PRC',
'PACKAGE' ,'PKG',
'FUNCTION' ,'FUN'))type ,line,rtrim(text) text
FROM all_source
WHERE type In ('PROCEDURE', 'FUNCTION','PACKAGE')
AND text like v_search
ORDER BY owner,name,line,text;
ELSE
RAISE_APPLICATION_ERROR(-20000, 'Input not correct');
END IF;
LOOP
FETCH v_linescan INTO v_search;
EXIT WHEN v_CursorVar%NOTFOUND;
INSERT INTO temp_table
VALUES (v_search.owner,v_search.name,v_search.type,v_search.line,v_search.text, sysdate);
END LOOP;
/* Close the cursor. */
CLOSE v_CursorVar;
COMMIT;
END scan_subpgm;
/
|
|
|
|
| Re: sql dynamic vs sql static [message #37757 is a reply to message #37744] |
Tue, 26 February 2002 22:53  |
MarcoC
Messages: 10
Registered: February 2002
|
Junior Member |
|
|
Hi Todd,
your answer made me curious about how would you code the same sql dynamically and would it be better or just another way to achieve ,in this context, the same thing?
Thanks
Marco
|
|
|
|
| Re: sql dynamic vs sql static [message #37770 is a reply to message #37757] |
Wed, 27 February 2002 08:01 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
To use dynamic SQL in your query, you would do something like:
open v_linescan for
'select owner, name, ...';
The select statement is just one big string. BUT, in your case, static works just fine because your only variable is in the where clause.
Static is always better than dynamic (for numerous reasons - parsing, re-use, etc.), so only resort to dynamic when you need to change any of the following in a statement (this is not an exhaustive list):
columns returned
name of tables
where conditions (as opposed to values)
order by clause
So, for example, a valid use of dynamic SQL is when you would pass the name of a table into a procedure and that name is part of the SQL statement. You can't use bind variables for objects, so you would do something like:
open x for 'select * from ' || p_table_name;
Hope this helps...
|
|
|
|
| Re: sql dynamic vs sql static [message #39360 is a reply to message #37757] |
Thu, 11 July 2002 07:27 |
Frank Quero
Messages: 1
Registered: July 2002
|
Junior Member |
|
|
Hi, before you begin to read this note i want to say you that: MY ENGLISH ISN`T GOOD, please excuse me, I have a problen with native dynamic sql; I have one procedures that return a parameter , and this parameter is long (data type) and the value is a query (string), the colunms of this query are dynamics; the problen is the follow: When I tray to execute then query ( for example: open v_cursor for v_sql_dyna), an exception ocurr an say:
'Error: ORA-01031: insufficient privileges
ORA-06512: at "ANALISTA_SIM.PK_SIM_BALANCE_MARGEN", line 427(linea del procedure PK_SIM_BALANCE_MARGEN.p_sim_prueba donde se abre el cursor) ORA-06512: at line 28, Batch 1 Line 1 Col 1'.
I take then value of then parameter (the query) and execute in sqlplus and in another tools and work; I changue the query in the procedure and work (I meen open the cursor). All this is in the same database with the same user, this user isn`t the owner of the table but, all this test was performed with the owner of the objects with DBA Rol. what can I do?; I talk with the DBA experts of my company, and they don`t have Answers about this; I need help please(FAST AS YOU CAN), becuse this is necesary for finish a part important of a proyect.
Waithing for your help
Frank Quero.
Intesa - Venezuela
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 00:00:51 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
