New answers tagged plsql
on the destination database check that the origin database is listed in the tnsnames.ora Create a database link on the destination database to the origin database with a user on the origin database who has been granted select on the origin table. create a materialized view on the destination database which is filled with the statement select * from ...
On the machine where you installed your database you should have SQL*Plus installed. For your question. Create a database link from DB_NEW to DB_LIVE and use your Read Data user. Now you can query the data.
The problem is that the variable a that you declare in your declaration section is not the same as the variable a that is declared in the context of your loop. You've declared two different variables named a with different scope. If you run your block without a declaration section, you'll notice that it works even though you aren't declaring a variable a ...
for SQL developer (not sure about PL/SQL Developer), the way to see your output is through Output Variables log view. If you go to View->Log (Ctrl-Shift-L) that is where sql developer puts the results. To make it conducive to sql developer, I would recommend using the following execution method: DECLARE CV_RESULTS sys_refcursor; BEGIN SP_EMP( ...
It is the right command. Previously, it give error because of oracle's installation tables. BEGIN FOR t IN (select * from dba_tables where owner!='GGSTEST' AND owner not like '%SYS%' AND owner not like '%ORDDATA%' AND owner not like '%ADMIN%' AND owner not like '%APEX%' AND owner not like '%XDB%' AND owner not like '%FLOW_FILES%' AND owner not like ...
Naming your parameter the same way you named your column is quite unfortunate. SELECT salary INTO c_sal FROM customers WHERE id = ID; In the above statement, typing id in lowercase or ID in uppercase makes no difference, it is case insensitive. Just because you named your PL/SQL parameter ID (in uppercase), does not mean the database will use ...
This mean query: SELECT salary INTO c_sal return more than 1 row check the table for count records with ID = 1 if ID by any legal reason non unique - need make LOOP
I try to grant all privileges of all tables to a user. Why? And in future I don't need to add any privileges to that user. Yes, you will. As soon as you add another table, you have to repeat the whole process and you have to do so for each and every User. I would suggest that this is not an efficient use of your time. I need to do this. ...
the problem is due to characters being interpreted by PL/SQL, like the dollar sign or the hash tag. By adding double quotes, you won't get the error anymore. Regards.
GRANT ALL PRIVILEGES ON .... is MySQL privs syntax. You need to do: GRANT ALL ON SCHEMA.TABLE TO USER See the documentation.
You could try using select * from table(xmlsequence( myfunc() )) . In this case you get xml in columns. Or try this example to extract field COL from your refcursor select extractvalue(COLUMN_VALUE ,'/ROW/COL') from table( xmlsequence( cursor( select 1 COL from dual union all select 2 COL from dual) )) . Here cursor(..) is equivalent of refcursor ...
Top 50 recent answers are included